609
社区成员
发帖
与我相关
我的任务
分享
-- 使用以下的数据源来制作交叉报表,rows选择dept_code,columns选择g1与g2,values使用v1(注意不是count(v1))即可。
select DEPT_CODE,'工作' g1,job g2,count(*) v1 from staff_DICT group by dept_code,job
union all
select DEPT_CODE,'籍贯' g1,address g2,count(*) from staff_DICT group by dept_code,address
union all
select DEPT_CODE,'工龄' g1,'1-20' g2,count(*) from staff_DICT group by dept_code,work_age where work_age <= 20
union all
select DEPT_CODE,'工龄' g1,'21-40' g2,count(*) from staff_DICT group by dept_code,work_age where work_age <= 40 and work_age > 20
union all
select DEPT_CODE,'工龄' g1,'40年以上' g2,count(*) from staff_DICT group by dept_code,work_age where work_age > 40
;
select 部门.编码, 部门.名称, sum(case 员工.性别 when '男' then 1 else 0 end) as 男,
sum(case 员工.性别 when '女' then 1 else 0 end) as 女,
sum(case when 员工.年龄 < 20 then 1 else 0 end) as 20岁以下,
....
....
from 部门, 员工
where 部门.编码 = 员工.部门编码
group by 部门.编码, 部门.名称