感觉挺难的汇总有关问题
感觉挺难的汇总问题
如果把auditC等于大Y时,auditor汇总,然后将汇总的数据加到现在的结果集中。
我可能表达的不是很清楚,我要的正确结果应该是如下:
部门 录入者 总数
-------------------------------------------------
PMC SHIY 3
PMC XIANGYM 2
采购部 WANGXX 3
------解决方案--------------------
--构建测试环境
--表A
if OBJECT_ID('tableA') is not null
drop table tableA
create table tableA
(
id_num nvarchar(20),
auditC nvarchar(10),
auditor nvarchar(10),
creator nvarchar(10),
department nvarchar(10)
)
go
insert into tableA
select '511-121100189','y','','SHIY','PMC'
union all
select '511-121100200','Y','WANGXX','XIANGYM','PMC'
union all
select '514-121100028','y','','XIANGYM','PMC'
--表B
if OBJECT_ID('tableB') is not null
drop table tableB
create table tableB
(
id_num nvarchar(20),
auditC nvarchar(10),
auditor nvarchar(10),
creator nvarchar(10),
department nvarchar(10)
)
go
insert into tableB
select '513-121100012','y','','SHIY','PMC'
union all
select '513-121100211','Y','WANGXX','SHIY','PMC'
union all
select '522-121100078','y','','WANGXX','采购部'
--SQL
SELECT HZ.部门,HZ.录入者,SUM(HZ.总数) 总数 FROM (
SELECT
department 部门,
creator 录入者,
COUNT(*) 总数
FROM tableA
GROUP BY department,tableA.creator
union all
SELECT
department 部门,
creator 录入者,
COUNT(*) 总数
FROM tableB
GROUP BY department,tableB.creator
) HZ
GROUP BY HZ.部门,HZ.录入者
ORDER BY HZ.部门 asc
select * from tableA
union all
select * from tableB
--结果
--部门 录入者 总数
-----------------------------------------------------
--PMC SHIY 3
--PMC XIANGYM 2
--采购部 WANGXX 1
--
--===================================================
--id_num auditC auditor creator department
-----------------------------------------------------
--511-121100189 y null SHIY PMC
--511-121100200 Y WANGXX XIANGYM PMC
--514-121100028 y null XIANGYM PMC
--513-121100012 y null SHIY PMC
--513-121100211 Y WANGXX SHIY PMC
--522-121100078 y null WANGXX 采购部
如果把auditC等于大Y时,auditor汇总,然后将汇总的数据加到现在的结果集中。
我可能表达的不是很清楚,我要的正确结果应该是如下:
部门 录入者 总数
-------------------------------------------------
PMC SHIY 3
PMC XIANGYM 2
采购部 WANGXX 3
------解决方案--------------------
select a.creator,c.department,qty from
(SELECT creator,count(*) qty from (
SELECT