if object_id('pubs..tb') is not null
drop table tb
go
create table tb(ID int,FileName varchar(20),FileType int,FileStatus int)
insert into tb(ID,FileName,FileType,FileStatus) values( 1, '登记表' ,1, 1)
insert into tb(ID,FileName,FileType,FileStatus) values( 2, '登记表' ,2, 2)
insert into tb(ID,FileName,FileType,FileStatus) values( 3, '监督方案' ,1, 2)
insert into tb(ID,FileName,FileType,FileStatus) values( 4, '安全培训记录' ,3, 1)
insert into tb(ID,FileName,FileType,FileStatus) values( 5, '安全培训记录' ,1, 2)
insert into tb(ID,FileName,FileType,FileStatus) values( 6, '专项方案1' ,2, 2)
insert into tb(ID,FileName,FileType,FileStatus) values( 7, '专项方案2 ' ,2, 2)
insert into tb(ID,FileName,FileType,FileStatus) values( 8, '专项方案2' ,3, 2)
go
select a1.阶段 , a1.总数 , isnull(a2.新建数,0) 新建数 , isnull(a3.上报数 , 0) 上报数 from
(select '阶段' + cast(filetype as varchar) as '阶段', count(*) 总数 from tb group by filetype) a1
left join
(select '阶段' + cast(filetype as varchar) as '阶段' , count(*) 新建数 from tb where filestatus = 1 group by filetype) a2
on a1.阶段 = a2.阶段
left join
(select '阶段' + cast(filetype as varchar) as '阶段' , count(*) 上报数 from tb where filestatus = 2 group by filetype) a3
on a1.阶段 = a3.阶段
drop table tb
/*
阶段 总数 新建数 上报数
---------------------------------- ----------- ----------- -----------
阶段1 3 1 2
阶段2 3 0 3
阶段3 2 1 1
(所影响的行数为 3 行)
*/