將三個表分組為一個主表和兩個詳細表

I.tblOwner詳情

II.tblListMaster

III.tblList詳情

IV.tblTagMaster

V.tblTagDetails

我想要的結果

這是我到目前為止嘗試過的查詢：

with cte_ListCount as
(
   Select o.Ownerid,
          Count(ld.OwnerId) as ListCount
   from tblOwnerDetails o
       inner join tblListDetail ld
           on o.OwnerId = ld.OwnerId
       inner join tblListMaster lm
           on lm.ListId = ld.ListId group by o.ownerid),
cte_TagCount as
(
   select o.ownerid,
          COUNT(td.OwnerId) as TagCount
       from  tblOwnerDetails o 
       left join tblTagDetail td
           on o.OwnerId = td.OwnerId
       inner join tblTagMaster tm
           on tm.TagId = td.TagId
       group by o.ownerid
)
select o.ownerid,
      ISNULL(clc.ListCount, 0) as ListCount,
      ISNULL(ctc.TagCount, 0) as TagCount
from tblOwnerDetails o
left join cte_ListCount clc
   on clc.OwnerId = o.OwnerId
left join cte_TagCount ctc
   on ctc.OwnerId = o.OwnerId

根據您迄今為止嘗試過的查詢，您可能會遇到計數不正確的問題，因為您要在關係的多方面加入兩個**一對多表，因為這兩個表都會導致多對-許多人加入。

相反，實現目標的一種方法是首先將每個表單獨匯總為兩個單獨的查詢，然後將結果連接在一起，如下所示：

WITH CTE_DetailList_Count AS -- Group up the Detail List and count them
(
   SELECT 
       Ownerid, 
       COUNT(listid) AS ListCount
   FROM detaillist
   GROUP BY ownerid
),
CTE_DetailTag_Count AS -- Group up the Detail Tags and count them
(
   SELECT 
       Ownerid, 
       COUNT(Tagid) AS TagCount
   FROM detailtag
   GROUP BY ownerid
)

-- Final results
SELECT 
   MT.Ownerid,
   ISNULL(DLC.ListCount, 0) AS ListCount,
   ISNULL(DTC.TagCount, 0) AS TagCount
FROM mastertable AS MT
LEFT JOIN CTE_DetailList_Count AS DLC
   ON MT.Ownerid = DLC.Ownerid
LEFT JOIN CTE_DetailTag_Count AS DTC
   ON MT.Ownerid = DTC.Ownerid

引用自：https://dba.stackexchange.com/questions/299134