常用的一些复杂SQL语句

开发者梓玥技术 2022年11月16日

0 收藏 798 点赞 3,367 浏览 1147 个字

1.根据表中的birthday统计年龄段人数：

//以下代码表示查询出来后的结果集添加一列字段cast('20以下' as char) as age

SELECT COUNT((DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y'))) as value,cast('20岁以下' as char) as name
            FROM hm_applicant WHERE (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y')) <= 20
UNION
SELECT COUNT((DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y'))) as value,cast('20-40岁' as char) as name
            FROM hm_applicant WHERE (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y')) > 20 AND (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y')) <= 40
UNION
SELECT COUNT((DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y'))) as value,cast('40-60岁' as char) as name
        FROM hm_applicant WHERE (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y')) > 40 AND (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y')) <= 60
UNION
SELECT COUNT((DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y'))) as value,cast('60-80岁' as char) as name
        FROM hm_applicant WHERE (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y')) > 60 AND (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y')) <= 80
UNION
SELECT COUNT((DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y'))) as value,cast('80岁以上' as char) as name
    FROM hm_applicant WHERE (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birthday,'%Y')) > 80

效果图如下图：