sql分段查询

最近做了点有意思的题目，纪录一下

查询每个部门工资最低的两个人的姓名、工资

select e1.ename,e1.sal,e1.deptno
from emp e1
where (select count(1) from emp e2 where e1.deptno=e2.deptno and e1.sal>e2.sal)<2
order by e1.deptno,e1.sal;
 

这个方法也可以找出每个部门工资排第几的人，把<改成=就好

原表

查询不同 工资段的员工个数

SELECT (
CASE WHEN sal<1000 THEN '1000以下'
WHEN sal>=1000 AND sal <2000 THEN '1000-2000'
WHEN sal>=2000 AND sal<3000 THEN '2000-3000'
else '3000以上'
END
 ) as 工资水平,COUNT(1)
FROM emp
GROUP BY 工资水平

用case where then来把表格转了一下

查询结果如下

统计收入水平人数
SELECT SUM(CASE WHEN sal>0 THEN 1 ELSE 0 END) 0元以上,
SUM(CASE WHEN sal>1000 THEN 1 ELSE 0 END) 1000元以上,
SUM(CASE WHEN sal>2000 THEN 1 ELSE 0 END) 2000元以上,
SUM(CASE WHEN sal>=3000 THEN 1 ELSE 0 END) 3000元以上
FROM emp

打印出各部门各工资段的人数
SELECT dname,SUM(CASE WHEN sal<1000 THEN 1 ELSE 0 END) '1000元以下',
SUM(CASE WHEN sal>=1000 and sal <2000 THEN 1 ELSE 0 END) '1000-2000',
SUM(CASE WHEN sal>=2000 and sal<3000 THEN 1 ELSE 0 END) '2000-3000',
SUM(CASE WHEN sal>=3000 THEN 1 ELSE 0 END) '3000元以上'
FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dept.deptno 
ORDER BY dname 

统计收入水平人数

SELECT (
CASE WHEN sal>0 THEN '0元以上'
ELSE 0
END
 ) as 工资水平,COUNT('工资水平')
FROM emp
UNION ALL
SELECT (
CASE WHEN sal>1000 THEN '1000元以上'
END
 ) as 工资水平,COUNT(1)
FROM emp
GROUP BY 工资水平
HAVING 工资水平 = '1000元以上'
UNION ALL
SELECT (
CASE WHEN sal>2000 THEN '2000元以上'
END
 ) as 工资水平,COUNT(1)
FROM emp
GROUP BY 工资水平
HAVING 工资水平 = '2000元以上'
UNION ALL
SELECT (
CASE WHEN sal>3000 THEN '3000元以上'
END
 ) as 工资水平,COUNT(1)
FROM emp
GROUP BY 工资水平
HAVING 工资水平 = '3000元以上'
UNION ALL
SELECT (
CASE WHEN sal>4000 THEN '4000元以上'
END
 ) as 工资水平,COUNT(1)
FROM emp
GROUP BY 工资水平
HAVING 工资水平 = '4000元以上'