--构建测试环境
--表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.部门 ascselect * 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
--表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.部门 ascselect * 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
from
(
select id_num,auditC,auditor,department from tableA
union all
select id_num,auditC,creator,department from tableA
union all
select id_num,auditC,auditor,department from tableB
union all
select id_num,auditC,creator,department from tableB
) as a
where auditC = 'y' and auditor <>''
group by department,auditor
order by department
/*
department auditor counts
---------- ---------- -----------
PMC SHIY 3
PMC WANGXX 2
PMC XIANGYM 2
采购部 WANGXX 1(4 行受影响)*/
现在已经按部门和录入者汇总了,我想把auditC等于Y时的数据再汇总一次并累加到前面的汇总。
(SELECT creator,count(*) qty from (
SELECT
department department,
creator creator
FROM tableA
union all
SELECT
department 部门,
auditor 录入
FROM tableA
where auditor<>'' and auditor<>creator union allSELECT
department 部门,
creator 录入者FROM tableB
union all
SELECT
department 部门,
auditor 录入FROM tableB
where auditor<>'' and auditor<>creator)
a group by creator) a,(
SELECT distinct
department,
creator from
(SELECT distinct
rtrim(department) department,
rtrim(creator) creatorFROM tableA union allSELECT
rtrim(department) 部门,
rtrim(creator) 录入者FROM tableB ) b ) c
where c.creator=a.creator