-- 如果你的性别与工资级别的统计不重叠的话,试试这样写
select dept,count(*),
count(decode(sex,'male',1,0)) male,
count(decode(sex,'female',1,0)) female,
count(decode(floor(salary/1000),1,1,0)) above1000,
count(decode(floor(salary/1000),2,1,0)) above2000,
count(decode(floor(salary/1000),3,1,0)) above3000,
count(decode(floor(salary/4000),0,0,1) above4000
from your_table
group by dept;
select dept,count(*),
count(decode(sex,'male',1,0)) male,
count(decode(sex,'female',1,0)) female,
count(decode(floor(salary/1000),1,1,0)) above1000,
count(decode(floor(salary/1000),2,1,0)) above2000,
count(decode(floor(salary/1000),3,1,0)) above3000,
count(decode(floor(salary/4000),0,0,1) above4000
from your_table
group by dept;
解决方案 »
- 用not exists 取最大值为什么没用
- oracle 导入请教(急)
- [求助]关于在windows下面用bat文件备份数据库的问题
- createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT)第三个参数
- oracle自动恢复连接???在线等待,仅有的20分全给
- 请高手帮助完成以下SQL的写法
- EXCEL数据到ORACLE
- 数据导出工作执行失败(我是新手,大家帮帮忙,给分。在线等)
- [菜鸟提问]关于SQLPlus Worksheet中输出乱码的问题
- 帮忙up也送分!莫名其妙的问题:Call命令不能用?
- 这个SQL语句应该怎么写?
- pl/sql中的一个问题
above1000为:1000-1999;
above2000为:2000-2999;
above3000为:3000-3999;
above4000为:4000->
用单句sql可以这样写:select dept, count(emp) total,
sum(decode(sex,'male',1,0)) male,
sum(decode(sex,'female',1,0)) female,
sum(decode(sign(salary-1000),-1,0,1)*decode(sign(salary-2000),-1,1,0)) above1000,
sum(decode(sign(salary-2000),-1,0,1)*decode(sign(salary-3000),-1,1,0)) above2000,
sum(decode(sign(salary-3000),-1,0,1)*decode(sign(salary-4000),-1,1,0)) above3000,
sum(decode(sign(salary-4000),-1,0,1) above4000
from table group by dept随手写的,你看看能不能用。。
male, female, above1000, above2000, above3000, above4000四个字段的聚组不能用count,要用sum。。
sum(case when sex = 'male' then 1 else 0 end) as male,
sum(case when sex='female' then 1 else 0 end) as female,
sum(case when salary >= 1000 and salary < 2000 then 1 else 0 end) as above1000,
sum(case when salary >= 2000 and salary < 3000 then 1 else 0 end) as above2000,
sum(case when salary >= 3000 and salary < 4000 then 1 else 0 end) as above3000,
sum(case when salary >= 4000 then 1 else 0 end) as above4000
from yourtable
group by dept;
decode没有这个限制
有必要的时候用case也无妨,对速度几乎不会有影响