一个SQL语句的问题

[waiqiuqiu]

表EMP（ENAME，JOB，DEPTNO，SAL）
问，1，工资SAL低于同工作平均数的人名
2。按工资的多少分组（1000-1500，1501-2000，2000-5000，5000以上）并按DEPTNO排序。

[azsoft]

select
(case when SAL between 1000 and 1500 then 1 end) '1000-1500',
(case when SAL between 1501 and 2000 then 1 end) '1501-2000',
(case when SAL between 2000 and 5000 then 1 end) '2000-5000',
(case when SAL > 5000 then 1 end) '5000以上'

from EMP order by DEPTNO

[gmlxf]

1:
select ENAME from EMP a
where SAL<(select avg(SAL) from EMP where JOB=a.JOB)

[gmlxf]

2：不怎么明白意思。
select * from EMP where SAL between 1000 and 1500
select * from EMP where SAL between 1501 and 2000
select * from EMP where SAL between 2000 and 5000
select * from EMP where SAL > 5000

还是要统计人数？？```

[waiqiuqiu]

不用，但要用一个语句

[waiqiuqiu]

没看到分组吗？就是按钱数和部门区分开

[gmlxf]

把你想要得到的结果的字段在下面列一下，不明白。

[waiqiuqiu]

那你就按照各个部门的工资区间，把人数统计出来吧？我看对我来说帮助足够了，在此多谢

[gmlxf]

--行转列：

select
count(case when SAL between 1000 and 1500 then 1 end) '1000-1500',
count(case when SAL between 1501 and 2000 then 1 end) '1501-2000',
count(case when SAL between 2000 and 5000 then 1 end) '2000-5000',
count(case when SAL > 5000 then 1 end) '5000以上'
from EMP
--或
select
sum(case when SAL between 1000 and 1500 then 1 end) '1000-1500',
sum(case when SAL between 1501 and 2000 then 1 end) '1501-2000',
sum(case when SAL between 2000 and 5000 then 1 end) '2000-5000',
sum(case when SAL > 5000 then 1 end) '5000以上'
from EMP

[dafu71]

select sum(case SAL between 1000 and 1500 then 1 else 0 end ) as '1000-1500',
sum(case SAL between 1501 and 2000 then 1 else 0 end ) as '1501-2000',
sum(case SAL between 2000 and 5000 then 1 else 0 end ) as '2000-5000',sum(case SAL >5000 then 1 else 0 end ) as '5000以上' from EMP