一对多联合查询导致查询数量增加

今天联合查询某条数据数量时，发现有些联合查询后数量会变多

出现错误的语句：

UPDATE Tmp
SET Tmp.DD_sftCount = ISNULL(ImpSql.Counts),Tmp.DD_MZXS = ISNULL(ImpSql.ItemsCounts,0)
FROM #tmp_Pjddmzl AS Tmp
LEFT JOIN (SELECT a.GuestId,COUNT(a.ID) AS Counts,COUNT(b.RepairSpecApplyId) AS ItemsCounts
    FROM Service_RepairSpecApply AS a
    LEFT JOIN Service_RepairSpecApply_Items AS b ON b.RepairSpecApplyId = a.ID
    GROUP BY a.GuestId)
AS ImpSql ON ImpSql.GuestId = Tmp.GuestId

查阅资料时突然想到

Service_RepairSpecApply与Service_RepairSpecApply_Items是一对多的对应关系，查询中主表会有多个Items，故Counts列会重复。

需要使用DISTINCT或者GROUP BY等进行去重或转化为聚合查询

修改后：

UPDATE Tmp
SET Tmp.DD_sftCount = ISNULL(ImpSql.Counts),Tmp.DD_MZXS = ISNULL(ImpSql.ItemsCounts,0)
FROM #tmp_Pjddmzl AS Tmp
LEFT JOIN (SELECT a.GuestId,COUNT(DISTINCT a.ID) AS Counts,COUNT(b.RepairSpecApplyId) AS ItemsCounts
    FROM Service_RepairSpecApply AS a
    LEFT JOIN Service_RepairSpecApply_Items AS b ON b.RepairSpecApplyId = a.ID
    GROUP BY a.GuestId)
AS ImpSql ON ImpSql.GuestId = Tmp.GuestId

结果：

手撸SQL真的好累阿(～﹃～)~zZ

表数量一多一犯迷糊就会出问题＞︿＜