我有一個 SQL 查詢，它給了我這樣的輸出：
詢問：
SELECT LineBaseOP.LineName, COUNT(CekimOzelligiBasedOP.RecID) * 3 AS CekimSayisi3Var
FROM LineBaseOP INNER JOIN
CekimOzelligiBasedOP ON LineBaseOP.LineName = CekimOzelligiBasedOP.LineName AND LineBaseOP.OperationNo = CekimOzelligiBasedOP.OperationNo AND
LineBaseOP.MachineName = CekimOzelligiBasedOP.MachineNumber
WHERE (LineBaseOP.FactoryId = 1)
GROUP BY LineBaseOP.LineName
輸出：
LineName|CekimSayisi3Var|
-------- ---------------
L11 | 255 |
L2 | 255 |
L3 | 43 |
L4 | 143 |
L5 | 121 |
L6 | 101 |
我正在嘗試像這樣對輸出進行分組：
L2,L3,L4,L5 作為 FH
L11、L6 為 SH
所以我想要的輸出需要看起來像：
GroupName|CekimSayisi3VarGrouped|
--------- ----------------------
FH | 562 |
SH | 356 |
關于如何實作這一目標的任何建議？
uj5u.com熱心網友回復：
您可以使用CASE運算式進行聚合：
SELECT
CASE WHEN lbop.LineName IN ('L2', 'L3', 'L4', 'L5')
THEN 'FH'
WHEN lbop.LineName IN ('L6', 'L11')
THEN 'SH' END AS LineName,
COUNT(cobop.RecID) * 3 AS CekimSayisi3Var
FROM LineBaseOP lbop
INNER JOIN CekimOzelligiBasedOP cobop
ON lbop.LineName = cobop.LineName AND
lbop.OperationNo = cobop.OperationNo AND
lbop.MachineName = cobop.MachineNumber
WHERE
lbop.FactoryId = 1
GROUP BY
CASE WHEN lbop.LineName IN ('L2', 'L3', 'L4', 'L5')
THEN 'FH'
WHEN lbop.LineName IN ('L6', 'L11')
THEN 'SH' END;
uj5u.com熱心網友回復：
一種更好的語法，避免重復GROUP BY值的需要，是將其放入CROSS APPLY
SELECT
v.GroupName,
COUNT(cobop.RecID) * 3 AS CekimSayisi3Var
FROM LineBaseOP lbop
INNER JOIN CekimOzelligiBasedOP cobop
ON lbop.LineName = cobop.LineName AND
lbop.OperationNo = cobop.OperationNo AND
lbop.MachineName = cobop.MachineNumber
CROSS APPLY (VALUES (
CASE WHEN lbop.LineName IN ('L2', 'L3', 'L4', 'L5')
THEN 'FH'
WHEN lbop.LineName IN ('L6', 'L11')
THEN 'SH' END
)) v(GroupName)
WHERE
lbop.FactoryId = 1
GROUP BY
v.GroupName;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/432831.html