34,590
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#table1') is null
drop table #table1
Go
Create table #table1([id] int,[name1] nvarchar(22),[name2] nvarchar(22),[name3] nvarchar(23),[name4Numb] int)
Insert #table1
select 1,N'aa',N'a1',N'a11',5 union all
select 2,N'aa',N'a2',null,4 union all
select 3,N'bb',N'b1',null,3 union all
select 4,N'bb',N'b2',null,3 union all
select 5,N'cc',N'c1',null,4
Go
if not object_id(N'Tempdb..#table2') is null
drop table #table2
Go
Create table #table2([id] int,[table1name2] nvarchar(22),[name4] nvarchar(24))
Insert #table2
select 1,N'a1',N'ddd' union all
select 2,N'a1',null union all
select 3,N'b1',null union all
select 4,N'c1',N'cccc'
Go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#table3') is null
drop table #table3
Go
Create table #table3([id] int,[table2id] int,[name5] nvarchar(24))
Insert #table3
select 1,1,N'eee' union all
select 2,1,N'ffff' union all
select 3,2,N'tttt' union all
select 4,3,N'yyyy'
Go
SELECT a.[name1] ,
COUNT(*) AS 总记录数 ,
SUM(CASE WHEN [name3] IS NULL THEN 1
ELSE 0
END) AS 未完成记录数1 ,
SUM(a.[name4Numb] - ISNULL(b.con2, 0)) AS 未完成记录数2 ,
SUM((a.[name4Numb] - ISNULL(b.con2, 0)-ISNULL(b.con3,0))) AS 未完成记录数4
FROM #table1 AS a
LEFT JOIN ( SELECT b1.[table1name2] ,
COUNT(DISTINCT b1.[name4]) AS con2 ,
COUNT(CASE WHEN b1.name4 IS NULL THEN b2.table2id END) AS con3
FROM #table2 AS b1
LEFT JOIN (SELECT DISTINCT table2id FROM #table3 ) AS b2 ON b2.[table2id] = b1.id
GROUP BY b1.[table1name2]
) AS b ON a.[name2] = b.[table1name2]
GROUP BY a.[name1];
/*
name1 总记录数 未完成记录数1 未完成记录数2 未完成记录数3
aa 2 1 8 7
bb 2 2 6 5
cc 1 1 3 3
*/