假設我們有如下資料
EmpId Stages Amount
101 Stg1 10.2
101 Stg2 10.22
101 Stg1 10.11
101 Stg3 6.21
101 Stg2 3.22
102 Stg1 3.23
102 Stg2 2.22
102 Stg3 1.22
102 Stg3 3.22
所需的結果：每個員工的明智階段計數和階段總和金額如下
EmpId Stg1-Count Amount-stg1 Stg2-Count Amount-stg2 Stg3-Count Amount-stg3
101 2 20.31 2 13.44 1 6.21
102 1 3.23 1 2.22 2 4.44
uj5u.com熱心網友回復：
這是一種使用條件聚合的簡單語法的方法。
/* Set up the sample data */
DROP TABLE IF EXISTS #RawData;
CREATE TABLE #RawData
(
EmpId INT,
Stages NVARCHAR(5),
Amount DECIMAL(9, 4)
);
INSERT INTO #RawData
VALUES (101, N'Stg1', 10.2),
(101, N'Stg2', 10.22),
(101, N'Stg1', 10.11),
(101, N'Stg3', 6.21),
(101, N'Stg2', 3.22),
(102, N'Stg1', 3.23),
(102, N'Stg2', 2.22),
(102, N'Stg3', 1.22),
(102, N'Stg3', 3.22),
(103, N'Stg1', 1.55);
我添加了一行以確保我們處理不完整和正在進行的階段進展。
這假設有固定數量的“階段”或類別將被計算和匯總。如果有大量或不斷變化的值，設定起來可能會變得乏味，但代碼非常簡單：
SELECT d.EmpId,
SUM(CASE WHEN d.Stages = 'Stg1' THEN 1 ELSE 0 END) AS [Stg1-Count],
SUM(CASE WHEN d.Stages = 'Stg1' THEN ISNULL(d.Amount, 0) ELSE 0 END) AS [Amount-Stg1],
SUM(CASE WHEN d.Stages = 'Stg2' THEN 1 ELSE 0 END) AS [Stg2-Count],
SUM(CASE WHEN d.Stages = 'Stg2' THEN ISNULL(d.Amount, 0) ELSE 0 END) AS [Amount-Stg2],
SUM(CASE WHEN d.Stages = 'Stg3' THEN 1 ELSE 0 END) AS [Stg3-Count],
SUM(CASE WHEN d.Stages = 'Stg3' THEN ISNULL(d.Amount, 0) ELSE 0 END) AS [Amount-Stg3]
FROM #RawData AS d
GROUP BY d.EmpId;
| EmpId | Stg1-計數 | 金額-Stg1 | Stg2-計數 | 金額-Stg2 | Stg3-計數 | 金額-Stg3 |
|---|---|---|---|---|---|---|
| 101 | 2 | 20.3100 | 2 | 13.4400 | 1 | 6.2100 |
| 102 | 1 | 3.2300 | 1 | 2.2200 | 2 | 4.4400 |
| 103 | 1 | 1.5500 | 0 | 0.0000 | 0 | 0.0000 |
它使用GROUP BYEmpId 上的 a 來聚合每個員工的所有內容，并CASE評估 s 以構造附加列。使用 aSUM而不是 aCOUNT提供了條件計數，否則我們只會獲取該員工的所有行的計數，而不管“階段”值如何。
uj5u.com熱心網友回復：
一種選擇是使用動態 SQL 并PIVOT獲得更具彈性的結果集。
使用包含一些新“階段”值的結果集的改編版本
/* Set up the sample data */
DROP TABLE IF EXISTS #RawData;
CREATE TABLE #RawData
(
EmpId INT,
Stages NVARCHAR(5),
Amount DECIMAL(9, 4)
);
DECLARE @SqlStatement NVARCHAR(MAX);
INSERT INTO #RawData
VALUES (101, N'Stg1', 10.2),
(101, N'Stg2', 10.22),
(101, N'Stg1', 10.11),
(101, N'Stg3', 6.21),
(101, N'Stg2', 3.22),
(102, N'Stg1', 3.23),
(102, N'Stg2', 2.22),
(102, N'Stg3', 1.22),
(102, N'Stg3', 3.22),
(103, N'Stg0', 4.00),
(103, N'Stg1', 1.55),
(103, N'Stg4', 14.00);
接下來，我們需要一些結構來處理動態 SQL 中的這些資料：
/* Set up some variables to store comma-delimited values and column names */
DECLARE @stages NVARCHAR(255),
@amountAlias NVARCHAR(255),
@countAlias NVARCHAR(255),
@resultCols NVARCHAR(255);
/* Store comma-delimited column names and selection aliases in variables for use in the dynamic blocks */
SELECT @stages = STRING_AGG(d.Stages, ','),
@amountAlias = STRING_AGG(CONCAT(N'ISNULL(a.', d.Stages, N', 0) AS [Amount-', d.Stages, N']'), ', '),
@countAlias = STRING_AGG(CONCAT(N'ISNULL(c.', d.Stages, N', 0) AS [', d.Stages, N'-Count]'), ', '),
@resultCols = STRING_AGG(CONCAT(N'c.[', d.Stages, N'-Count], a.[Amount-', d.Stages, N']'), ', ')
FROM (SELECT DISTINCT Stages FROM #RawData) AS d;
然后，就是使用這些變數構建動態代碼的問題：
SELECT @SqlStatement = N';WITH Amounts AS (SELECT a.EmpId,' @amountAlias
N'
FROM (SELECT EmpId, Stages, ISNULL(Amount, 0) AS Amount FROM #RawData) AS src
PIVOT (SUM(src.Amount)
FOR src.Stages IN (' @stages N') ) AS a),
Counts AS (SELECT c.EmpId,' @countAlias
N'
FROM (SELECT EmpId, Stages, ISNULL(Amount, 0) AS Amount FROM #RawData) AS src
PIVOT (COUNT(src.Amount)
FOR src.Stages IN (' @stages N') ) AS c)
SELECT a.EmpId, ' @resultCols N'
FROM Amounts AS a
JOIN Counts AS c
ON a.EmpId = c.EmpId;';
最后，您可以使用執行動態 SQLEXEC (@SqlStatement);
在此示例中，使用操作定義了兩個 CTE PIVOT：一個用于金額聚合，另一個用于計數。列名字串是使用不同的“階段”值構造的，并使用關聯的聚合型別名稱作為別名。
此解決方案具有調整并回傳新的“階段”值的優點，但更復雜并生成更復雜的執行計劃、更多的表掃描、更多的邏輯讀取，并且在更大規模時會遇到基數估計限制。
| EmpId | Stg0-Count | Amount-Stg0 | Stg1-Count | Amount-Stg1 | Stg2-Count | Amount-Stg2 | Stg3-Count | Amount-Stg3 | Stg4-Count | Amount-Stg4 |
|-------|------------|-------------|------------|-------------|------------|-------------|------------|-------------|------------|-------------|
| 101 | 0 | 0.0000 | 2 | 20.3100 | 2 | 13.4400 | 1 | 6.2100 | 0 | 0.0000 |
| 102 | 0 | 0.0000 | 1 | 3.2300 | 1 | 2.2200 | 2 | 4.4400 | 0 | 0.0000 |
| 103 | 1 | 4.0000 | 1 | 1.5500 | 0 | 0.0000 | 0 | 0.0000 | 1 | 14.0000 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/492725.html