浦发大佬曾问我一个问题,一个表只有成绩和姓名,要求统计出各分数段的总人数:这个sql 当时没写出来
现补上:
select case when sal between 0 and 1500 then '10-20'
when sal between 1500 and 3000 then '21-30'
else '81-100' end, count(0) 人数
from emp
group by case when sal between 0 and 1500 then '10-20'
when sal between 1500 and 3000 then '21-30'
else '81-100' end
order by count(0)
还有个方式:比较两个写法 上面一个语句的order by 可以不要!!
SELECT (case when sal >= 0 and sal < 1500 then '10-20'
when sal >= 1500 and sal < 3000 then '20-30'
else 'other' end) as cases, count(1) as "人数"
from emp
WHERE sal >= 900 and sal < 8000
GROUP BY (case when sal >= 0 and sal < 1500 then '10-20'
when sal >= 1500 and sal < 3000 then '20-30'
else 'other' end);
select case
when sal >= 0 and sal < 1500 then
'[0,1500)'
when sal >= 1500 and sal < 3000 then
'[1500,3000)'
else
'[other)'
end sal_ranges,
count(0)
from emp
group by case
when sal >= 0 and sal < 1500 then
'[0,1500)'
when sal >= 1500 and sal < 3000 then
'[1500,3000)'
else
'[other)'
end;