活动表:活动表ID,部门表ID,工程表ID,状态ID(0:草稿、1:进展中、2:审批通过、-1:审批不通过)
工程表:工程表ID
部门表:部门表ID已知条件:工程表有固定9条记录,工程ID从1001-1009九大工程;部门表固定22条记录,就是有22个部门要求:查询 所有部门,部门所对应的工程的活动记录数(1.活动状态必须为审批通过,2.需要消除重复项工程ID:就是一个部门上传多条活动记录,最终工程ID相同且审批状态为通过的记录数无论多少条都算1)
查询结果集:(0/9: /9是固定的因为,总共9项记录,/前面的数量是算出来的)
工程表:工程表ID
部门表:部门表ID已知条件:工程表有固定9条记录,工程ID从1001-1009九大工程;部门表固定22条记录,就是有22个部门要求:查询 所有部门,部门所对应的工程的活动记录数(1.活动状态必须为审批通过,2.需要消除重复项工程ID:就是一个部门上传多条活动记录,最终工程ID相同且审批状态为通过的记录数无论多少条都算1)
查询结果集:(0/9: /9是固定的因为,总共9项记录,/前面的数量是算出来的)
A部门 0/9
B部门 2/9
C部门 0/9
D部门 3/9
E部门 0/9
.... ...
意思就是某部门如果有所对应的工程活动且审批通过的就 数量/9 如果该部门没有上传活动或者状态没有审批通过的就显示0/9
insert into 活动表 select 1,1,1,2 union all select 2,1,1,2 --两条记录,一个工程
union all select 3,1,2,2 union all select 4,3,2,2
go
select 部门表id,count(distinct 工程表ID)工程数 from 活动表 where 状态ID=2 group by 部门表id
/*
部门表id 工程数
----------- -----------
1 1
2 2(2 行受影响)*/
go
drop table 活动表
insert into 活动表 select 1,1,1,2 union all select 2,1,1,2 --两条记录,一个工程
union all select 3,1,2,2 union all select 4,3,2,2
go
select 部门表id,convert(varchar,isnull(count(distinct 工程表ID),0))+'/9' 工程数 from 活动表 where 状态ID=2 group by 部门表id
/*
部门表id 工程数
----------- --------------------------------
1 1/9
2 2/9(2 行受影响)*/
go
drop table 活动表
insert into 活动表 select 1,1,1,2 union all select 2,1,1,2 --两条记录,一个工程
union all select 3,1,2,2 union all select 4,3,2,2
union all select 5,3,3,-1 --此工程不算,为0
go
select 部门表id,convert(varchar,isnull(count(distinct (case when 状态ID=2 then 工程表ID end)),0))+'/9' 工程数 from 活动表 group by 部门表id
/*
部门表id 工程数
----------- --------------------------------
1 1/9
2 2/9
3 0/9
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)*/
go
drop table 活动表
insert into 部门表 select 1,'a' union all select 2,'b' union all select 3,'c'
union all select 4,'d' union all select 5,'e' --此两部门没有条目
create table 活动表(id int,工程表id int,部门表ID int,状态ID int)
insert into 活动表 select 1,1,1,2 union all select 2,1,1,2 --两条记录,一个工程
union all select 3,1,2,2 union all select 4,3,2,2
union all select 5,3,3,-1 --此工程不算,为0
go
select a.id as 部门表id,convert(varchar,isnull(count(distinct (case when b.状态ID=2 then b.工程表ID end)),0))+'/9' 工程数 from 部门表 a left join 活动表 b on a.id=b.部门表ID group by a.id
/*
部门表id 工程数
----------- --------------------------------
1 1/9
2 2/9
3 0/9
4 0/9
5 0/9
警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)*/
go
drop table 活动表,部门表
insert into 部门表 select '部门a' union all select '部门b' union all select '部门c'
union all select '部门d' union all select '部门e' --此两部门没有条目
create table 活动表(id int,工程表id int,部门表ID varchar(10),状态ID int)
insert into 活动表 select 1,1,'部门a',2 union all select 2,1,'部门a',2 --两条记录,一个工程
union all select 3,1,'部门b',2 union all select 4,3,'部门b',2
union all select 5,3,'部门c',-1 --此工程不算,为0
go
select a.id as 部门表id,convert(varchar,isnull(count(distinct (case when b.状态ID=2 then b.工程表ID end)),0))+'/9' 工程数 from 部门表 a left join 活动表 b on a.id=b.部门表ID group by a.id
/*
部门表id 工程数
---------- --------------------------------
部门a 1/9
部门b 2/9
部门c 0/9
部门d 0/9
部门e 0/9
警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)*/
go
drop table 活动表,部门表