去面试遇到一个sql问题

去面试遇到一个sql问题

select case when (工资 >=1000 and 工资 <=1999) then '1000-1999'
when (工资 >=2000 and 工资 <=2999) then '2000-2999'
else '3000-3999' end '工资范围', count(*) 人数 from 工资表
group by
case when (资 >=1000 and 工资 <=1999) then '1000-1999
when (工资 >=2000 and 工资 <=2999) then '2000-2999'
else '3000-3999' end
order by 1
类似这样 当然你也可以用union all 来实现

select 人数 from (
select count(0) 人数 from 表 where between 工资 1000 and 1900 union all
select count(0) 人数 from 表 where between 工资 2000 and 2900 union all
select count(0) 人数 from 表 where between 工资 3000 and 3900
) as T

SELECt * FROM 表名 WHERe (工资>1000 and 工资<1999) OR (工资>2000 and 工资<2999) OR (工资>3000 and 工资<3999)

select 人数 from (
select count(0) 人数 from 表 where between 工资 1000 and 1900 union all
select count(0) 人数 from 表 where between 工资 2000 and 2900 union all
select count(0) 人数 from 表 where between 工资 3000 and 3900
) as T

假定 工资金额存放于工资表的金额字段，可以用下面语句统计各级别工资的人数：
SELECt 工资.金额 div 1000 * 1000 as 级别, count(*) as 人数
FROM 工资
where 金额 >= 1000 and 金额 <= 4000
group by 级别;
如果不需要限制金额范围（1000~4000），把其中的where语句去掉即可。