使用臨時表，先查詢出結果，在用于后面表的子查詢或者聯查

 
-- 刪除表1if   EXISTS ( SELECT 1  FROM tempdb.sys.objects where  name like '#temp_PublishRecord%' )  beginDROP TABLE  #temp_PublishRecordprint  '已刪除臨時表 #temp_PublishRecord'end--創建臨時表 1
SELECT  
[ArticleId],k.[FirstAuthorDeptId]
,[PublishChannel]into #temp_PublishRecord   --創建臨時表FROM  [PublishRecord] as  pleft join   [Article] as k on k.id=p.ArticleId where  -- k.[FirstAuthorDeptId]=m.[FirstAuthorDeptId] k.SendTime >= '2024-05-01' AND k.SendTime < '2025-01-01' and k.ArticleStatus!=1700 and k.ArticleStatus!=1600and k.VStatistic=1-- 刪除表2if   EXISTS ( SELECT 1  FROM tempdb.sys.objects where  name like '#temp_AdoptedRecord%' )  beginDROP TABLE  #temp_AdoptedRecordprint  '已刪除臨時表 #temp_AdoptedRecord'end--創建臨時表 2 
SELECT  [ArticleId],[Operatortime],[Channel_name],a.[FirstAuthorDeptId]into #temp_AdoptedRecordFROM  [AdoptedRecord] as bleft join Article as a on a.Id=b.ArticleId where   -- a.[FirstAuthorDeptId]='0443fea5641a453780e2e2780f2ff5b3'a.VStatistic=1 and a.ArticleStatus!=1700 and a.ArticleStatus!=1600and b.Operatortime>   '2024-05-01' AND  b.Operatortime < '2025-01-01'select   m.*,
-- 發布量   ( SELECT  count(distinct  [ArticleId] )  FROM #temp_PublishRecord   where  [FirstAuthorDeptId]=m.[FirstAuthorDeptId]) as publishNum-- 頭條新聞，數量 
,( SELECT  count(distinct  [ArticleId] )  FROM #temp_PublishRecord   where  [FirstAuthorDeptId]=m.[FirstAuthorDeptId] and [PublishChannel] like '頭條新聞%') as ttxwNum-- 重點關注，數量
,( SELECT  count(distinct  [ArticleId] )  FROM #temp_PublishRecord   where  [FirstAuthorDeptId]=m.[FirstAuthorDeptId] and [PublishChannel] like '重點關注%') as zdgzNum-- 動態信息，數量
,( SELECT  count(distinct  [ArticleId] )  FROM #temp_PublishRecord   where  [FirstAuthorDeptId]=m.[FirstAuthorDeptId] and [PublishChannel] like '動態信息%') as zdgzNum--測試欄目3 采納數
,(select count([ArticleId]) from #temp_AdoptedRecord 
where [FirstAuthorDeptId]=m.[FirstAuthorDeptId]  and [Channel_name]='測試欄目3'  
) as jtCount
--測試欄目2，采納數
,(select count([ArticleId]) from #temp_AdoptedRecord 
where [FirstAuthorDeptId]=m.[FirstAuthorDeptId]  and [Channel_name]='測試欄目2'  
) as fgsdtxwCount
--測試欄目1，采納數
,(select count([ArticleId]) from #temp_AdoptedRecord 
where [FirstAuthorDeptId]=m.[FirstAuthorDeptId]  and [Channel_name]='測試欄目1'  
) as  fgsjrywCountfrom (
SELECT   a.[FirstAuthorDeptId],d.OrganizationName,count(a.id	) as ReportCount  --報送量
FROM [Article] as a
left join Organization as d on d.Id=a.[FirstAuthorDeptId] 
where a.VStatistic=1 and a.ArticleStatus!=1700 and a.ArticleStatus!=1600
and a.[FirstAuthorDeptId]  is not null
and  a.SendTime >= '2024-05-01' AND a.SendTime < '2025-01-01'
group by  a.[FirstAuthorDeptId] ,d.OrganizationName
) as m