單選中的 T-SQL 計數

我有一張桌子叫Catalog. 例如，它有多個列，CreatedByID並且ModifiedByID包含使用者 ID。我想計算使用者創建和編輯了多少文件。

我花了幾個小時尋找替代方案，但找不到任何可行的方法。這可行，我得到正確的結果，但有沒有更簡單的方法來做到這一點？

SELECT l.UserName
    , US.Used
    , US.Created
    , US.MostRecent
    , US.Edited
   FROM [Users] US
   JOIN    (SELECT UserName
                 , COUNT(UserName) AS Used
                 , MAX([TimeStart]) AS MostRecent 
            FROM [Log] 
            WHERE ReportAction = 1 
            GROUP BY UserName) AS l
       ON (Us.UserName = l.UserName)

   JOIN    (SELECT CreatedByID
                 , COUNT(CreatedByID) AS Created 
            FROM [Catalog] 
            GROUP BY CreatedByID) AS Cat
       ON (Us.UserID = Cat.CreatedByID)

   JOIN    (SELECT ModifiedByID
                 , COUNT(ModifiedByID) AS Edited 
            FROM [Catalog] 
            GROUP BY ModifiedByID) AS Cat2
       ON (Us.UserID = Cat2.ModifiedByID)

我想同時獲得COUNT(CreatedByID)一個COUNT(ModifiedByID)選擇。例如，我可以得到使用者創建了多少文件：

SELECT CreatedByID, COUNT(CreatedByID) AS Created
FROM [Catalog]
GROUP BY CreatedByID

以及使用者編輯了多少文件：

SELECT ModifiedByID, COUNT(ModifiedByID) AS Modified
FROM [Catalog]
GROUP BY ModifiedByID

但是如何在一次選擇中執行這些操作？我已經嘗試過這樣的事情，但結果不再正確ModifiedByID。

SELECT CreatedByID, COUNT(CreatedByID) AS Created, COUNT(ModifiedByID) AS Modified
FROM [Catalog]
GROUP BY CreatedByID

您正在嘗試做的事情可以使用兩個操作來解決：unpivot 後跟一個 pivot。

對您要查找的查詢最重要的表Catalog是以文件為中心的。它以這種形式儲存數據：

What | Created by Whom | Edited by Whom

但是，您需要以使用者為中心的報告。因此，首先，您需要將原始集合轉換為以使用者為中心的集合：

Who | Did What | To What

在這種情況下，這To What部分可能並不是絕對必要的，但我將其保留是為了更好地說明數據在所尋求的轉換過程中是如何重新排列的。轉換本身稱為unpivot。假設t-sql您問題上的標記意味著 SQL Server 而不是 Sybase，那麼您的情況下的 unpivot 可以像這樣實現：

SELECT
 u.UserID,
 Action = LEFT(u.Action, LEN(u.Action) - 4)
FROM
 dbo.Catalog AS c
 UNPIVOT
 (
   UserID
   FOR Action IN (CreatedByID, ModifiedByID)
 ) AS u
;

LEFT() 技巧只是ByID從每個名稱中刪除位，只留下動詞。

另一種方式是這樣的：

SELECT
 u.UserID,
 u.Action
FROM
 dbo.Catalog AS c
 CROSS APPLY
 (
   VALUES
     (CreatedByID,  'Created'),
     (ModifiedByID, 'Modified')
 ) AS u (UserID, Action)
WHERE
 u.UserID IS NOT NULL
;

我假設未修改的文件在ModifiedByID. 過濾掉Modified具有空使用者 ID 的條目是上述查詢中的 WHERE 子句的目的。

現在您可以簡單地將結果分組UserID, Action併計算每組中的行數。這將為您提供以下形式的輸出值：

UserID  Action    Count
------  --------  -----
100     Created   10
100     Modified  15
110     Created   8
...     ...       ...

但如您所願Created並Modified作為單獨的列：

UserID  Created  Modified
------  -------  --------
100     10       15
...     ...      ...

您將需要使用pivot對結果進行分組。一種方法是使用本機 PIVOT 語法：

SELECT
 UserID,
 Created,
 Modified
FROM
 (
    ... /* the unpivot query (either option) */
 ) AS s
 PIVOT
 (
   COUNT(Action)
   FOR Action IN (Created, Modified)
 ) AS p
;

您也可以使用稱為條件聚合的舊語法來實現相同的目的：

SELECT
 UserID,
 Created  = COUNT(CASE Action WHEN 'Created'  THEN 1 END),
 Modified = COUNT(CASE Action WHEN 'Modified' THEN 1 END)
FROM
 (
    ... /* the unpivot query (either option) */
 ) AS s
GROUP BY
 UserID
;

在後一種情況下，可以避免派生表。相反，您只需要使用 FROM 子句，如有必要，還需要使用任一非透視方法中的 WHERE 子句。也就是說，最終的條件聚合查詢看起來像這樣：

SELECT
 u.UserID,
 Created  = COUNT(CASE u.Action WHEN 'Created'  THEN 1 END),
 Modified = COUNT(CASE u.Action WHEN 'Modified' THEN 1 END)
FROM
 dbo.Catalog AS c
 UNPIVOT
 (
   UserID
   FOR Action IN (CreatedByID, ModifiedByID)
 ) AS u
GROUP BY
 u.UserID
;

或像這樣：

SELECT
 u.UserID,
 Created  = COUNT(CASE u.Action WHEN 'Created'  THEN 1 END),
 Modified = COUNT(CASE u.Action WHEN 'Modified' THEN 1 END)
FROM
 dbo.Catalog AS c
 CROSS APPLY
 (
   VALUES
     (CreatedByID,  'Created'),
     (ModifiedByID, 'Modified')
 ) AS u (UserID, Action)
WHERE
 u.UserID IS NOT NULL
GROUP BY
 u.UserID
;

您可以將最終查詢用作派生表，將結果連接到Users表中，以進一步將它們與Log表中的結果連接起來——與您現在所做的方式大致相同。

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