相关知识回顾:
case函数:
--简单Case函数 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END
--Case搜索函数 CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END两者区别:这两种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。
if函数: if(条件判断,条件为真值,条件为假的值)
解题思路:
计算25岁及以上 和25岁以下的用户数量(NULL包含在25岁以下)
if条件判断:
select if( age>= 25,"25岁及以上","25岁以下") AS age_cut ,count(*) as number from user_profile group by age_cut;
case函数:
SELECT CASE WHEN age < 25&nbs***bsp;age IS NULL THEN '25岁以下' WHEN age >= 25 THEN '25岁及以上' END age_cut,COUNT(*)number FROM user_profile GROUP BY age_cut;
union联结:
select "25岁以下" as age_cut,count(*) as number from user_profile where age <25 or age is NULL union ALL select "25岁及以上" as age_cut, count(*) as number from user_profile where age >= 25;