oracle pl/sql:
SELECT GZ_LEVEL, COUNT(*)
FROM (SELECT DECODE(SIGN(GZ-800),1,'>800'
,DECODE(SIGN(GZ-600),1,'600-800'
,DECODE(SIGN(GZ-400),1,'400-600','NULL')
)
) GZ_LEVEL
FROM SALARY)
WHERE GZ_LEVEL != 'NULL'
GROUP BY GZ_LEVEL;比几条分开查询慢
SELECT GZ_LEVEL, COUNT(*)
FROM (SELECT DECODE(SIGN(GZ-800),1,'>800'
,DECODE(SIGN(GZ-600),1,'600-800'
,DECODE(SIGN(GZ-400),1,'400-600','NULL')
)
) GZ_LEVEL
FROM SALARY)
WHERE GZ_LEVEL != 'NULL'
GROUP BY GZ_LEVEL;比几条分开查询慢
UNION
Select Count(*) from tblName where GZ>600 and GZ<=800
UNION
Select Count(*) from tblName where GZ>800
UNION
select '600-800' as 范围, 人数 from (Select Count(*) as 人数 from tblName where GZ>600 and GZ<=800) as b
UNION
select '800-' as 范围, 人数 from (Select Count(*) as 人数 from tblName where GZ>800) as c
KingSunSha(弱水三千)才比较不错。
另外,如果是SQL Server,可将DECODE替换为CASE方式
select sum(P1.A) as '>800',sum(P1.B) as '600-800',sum(P1.C) as '<600' from
(
select
sum(case when GZ>800 then 1 else 0 end) as A,
sum(case when GZ>=600 and GZ<=800 then 1 else 0 end) as B,
sum(case when GZ<600 then 1 else 0 end) as C
from 你的表名
group by PID
) as P1
tablename
这样就把各个段的人数都求出来了。as 后面最好不要出现数字开头,可能会出错。因为列名不能以数字开头
TO: liujianjun_(流星尔)
IIF这个函数是什么数据库的?SQL SERVER?