1表结构
ID VARCHAR2(9) Y 员工id编号
NAME VARCHAR2(18) Y 名字
SEX CHAR(1) Y 性别
SALARY NUMBER(6,2) Y 薪水
BIRTHDAY DATE Y 出生日期
DAYJOB VARCHAR2(12) Y 职务
GRADE VARCHAR2(12) Y 职务等级
UNIT VARCHAR2(12) Y 部门
2测试数据
id name sex salary birghday dayjob grade unit
000000002 王雪梅 0 2000.00 2010-3-1 主管 1 销售
000000004 哈俣 0 3000.00 2010-3-17 员工 1 销售
000000005 刘更祥 1 1000.00 2010-3-10 主管 2 技术
000000006 环节 1 1500.00 2010-3-1 员工 1 技术
000000003 3 0 100.00 2010-3-16 经理 2 客服
000000001 刘祥 0 500.00 2010-3-25 主管 2 客服
3输出结果要求
部门 , 职务(dayjob)为主管且等级(grade)为1人数 ,职务(dayjob)为主管且等级(grade)为2(个数),职务(dayjob)为员工且等级(grade)为1(个数) ....
如：销售 1 0 1谢谢帮助！！！！！！！！！！！！！！！！
count(case when dayjob='主管' and grade='2' then 1 end),
count(case when dayjob='员工' and grade='1' then 1 end)
from table1
group by unit
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
Connected as billing
SQL>
SQL> with t as (
2 select '000000002' id , '王雪梅' name , 0 sex, 2000.00 salary, date '2010-3-1' birghday,'主管' dayjob, 1 grade, '销售' unit from dual union all
3 select '000000004' id , '哈俣' name , 0 sex, 3000.00 salary, date '2010-3-17' birghday,'员工' dayjob, 1 grade, '销售' unit from dual union all
4 select '000000005' id , '刘更祥' name ,1 sex, 1000.00 salary, date '2010-3-10' birghday,'主管' dayjob, 2 grade, '技术' unit from dual union all
5 select '000000006' id , '环节' name ,1 sex, 1500.00 salary, date '2010-3-1' birghday,'员工' dayjob, 1 grade, '技术' unit from dual union all
6 select '000000003' id , '3' name , 0 sex, 100.00 salary, date '2010-3-16' birghday,'经理' dayjob, 2 grade, '客服' unit from dual union all
7 select '000000001' id , '刘祥' name , 0 sex, 500.00 salary, date '2010-3-25' birghday,'主管' dayjob, 2 grade, '客服' unit from dual
8 )
9 select unit,
10 count(case when dayjob='主管' and grade='1' then 1 end),
11 count(case when dayjob='主管' and grade='2' then 1 end),
12 count(case when dayjob='员工' and grade='1' then 1 end),
13 count(case when dayjob='员工' and grade='2' then 1 end),
14 count(case when dayjob='经理' and grade='1' then 1 end),
15 count(case when dayjob='经理' and grade='2' then 1 end)
16 from t
17 group by unit
18 /UNIT COUNT(CASEWHENDAYJOB='主管'AND COUNT(CASEWHENDAYJOB='主管'AND COUNT(CASEWHENDAYJOB='员工'AND COUNT(CASEWHENDAYJOB='员工'AND COUNT(CASEWHENDAYJOB='经理'AND COUNT(CASEWHENDAYJOB='经理'AND
------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
销售 1 0 1 0 0 0
技术 0 1 1 0 0 0
客服 0 1 0 0 0 1SQL>
count(case when dayjob='主管' and grade='2' then 1 end),
count(case when dayjob='员工' and grade='1' then 1 end)
from table1
group by unit
up and study . wildwave 好久没见了啊..
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716
with t as (
select '000000002' id , '王雪梅' name , 0 sex, 2000.00 salary, date '2010-3-1' birghday,'主管' dayjob, 1 grade, '销售' unit from dual union all
select '000000004' id , '哈俣' name , 0 sex, 3000.00 salary, date '2010-3-17' birghday,'员工' dayjob, 1 grade, '销售' unit from dual union all
select '000000005' id , '刘更祥' name ,1 sex, 1000.00 salary, date '2010-3-10' birghday,'主管' dayjob, 2 grade, '技术' unit from dual union all
select '000000006' id , '环节' name ,1 sex, 1500.00 salary, date '2010-3-1' birghday,'员工' dayjob, 1 grade, '技术' unit from dual union all
select '000000003' id , '3' name , 0 sex, 100.00 salary, date '2010-3-16' birghday,'经理' dayjob, 2 grade, '客服' unit from dual union all
select '000000001' id , '刘祥' name , 0 sex, 500.00 salary, date '2010-3-25' birghday,'主管' dayjob, 2 grade, '客服' unit from dual
)
select unit "部门",
sum(decode(jobgrade, '主管1', cnt,0)) "主管级别1",
sum(decode(jobgrade, '主管2', cnt,0)) "主管级别2",
sum(decode(jobgrade, '员工1', cnt,0)) "员工级别1"
from (select unit, dayjob || grade jobgrade, count(*) cnt
from t
where (dayjob = '主管' and (grade = 1 or grade = 2))
or (dayjob = '员工' and grade = 1)
group by unit, dayjob || grade)
group by unit;