导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

一个SQL语句的问题

waiqiuqiu 2003-12-16 10:53:48
表EMP(ENAME,JOB,DEPTNO,SAL)
问,1,工资SAL低于同工作平均数的人名
2。按工资的多少分组(1000-1500,1501-2000,2000-5000,5000以上)并按DEPTNO排序。
...全文
5 点赞 收藏 9
写回复
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
dafu71 2003-12-16
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
回复
gmlxf 2003-12-16
--行转列:

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
回复
waiqiuqiu 2003-12-16
那你就按照各个部门的工资区间,把人数统计出来吧?我看对我来说帮助足够了,在此多谢
回复
gmlxf 2003-12-16
把你想要得到的结果的字段在下面列一下,不明白。
回复
waiqiuqiu 2003-12-16
没看到分组吗?就是按钱数和部门区分开
回复
waiqiuqiu 2003-12-16
不用,但要用一个语句
回复
gmlxf 2003-12-16
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

还是要统计人数??```
回复
gmlxf 2003-12-16
1:
select ENAME from EMP a
where SAL<(select avg(SAL) from EMP where JOB=a.JOB)
回复
azsoft 2003-12-16
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告