sql - 从多个表中选择计数

Question

我有三个表 TableThreeItems、TableTwoItems 和 TableOneItems，我需要获取 Person 在每个表中的访问次数。

我尝试了以下方法:

SELECT Person.[PersonId]

,  Count(TableOneItemId ) as TableOneItemCount
, Count(TableTwoItemIdId ) as TableTwoItemCount
, Count(TableThreeItemId )  as TableThreeItemCount


FROM  [dbo].[Person]
LEFT JOIN TableOneItem ON TableOneItem.PersonId=Person.PersonId
LEFT JOIN TableThreeItemId ON TableThreeItemId.PersonId=Person.PersonId
LEFT JOIN TableTwoItemId ON TableTwoItemId.PersonId=Person.PersonId
WHERE [Person].PersonId=1
GROUP BY Person.[PersonId]

但这并没有将其中每一个的计数视为单独的，但我总是错误的计数。例如，如果他有 1 个 TableOneItem 和 2 个 TableTwoItemId，则 TableOneItemCount 为 2 而 TableTwoItemIdCount 为 2

Answer 1

只需在计数中添加一个 DISTINCT 即可:

SELECT Person.[PersonId]

,  Count(DISTINCT TableOneItemId ) as TableOneItemCount
, Count(DISTINCT TableTwoItemIdId ) as TableTwoItemCount
, Count(DISTINCT TableThreeItemId )  as TableThreeItemCount


FROM  [dbo].[Person]
LEFT JOIN TableOneItem ON TableOneItem.PersonId=Person.PersonId
LEFT JOIN TableThreeItemId ON TableThreeItemId.PersonId=Person.PersonId
LEFT JOIN TableTwoItemId ON TableTwoItemId.PersonId=Person.PersonId
WHERE [Person].PersonId=1
GROUP BY Person.[PersonId]