如何在自己的列中獲取每個 OrderID 的唯一 CarNum 總數？
期望的結果：
當前結果：
下面是在“當前結果”影像中構建結果的代碼：
IF OBJECT_ID('tempdb..#testTable') IS NOT NULL DROP TABLE #testTable
CREATE TABLE #testTable
(
OrderID INT
, CarNum INT
, TimeOfDay VARCHAR(10)
, OrderNum INT
, TotalCarNum INT
)
INSERT INTO #testTable(OrderID, CarNum, TimeOfDay, OrderNum)
VALUES
(1111111,2069, 'AM', 1)
,(1111111,2199, 'AM', 2)
,(1111111,2147, 'AM', 3)
,(1111111,2147, 'PM', 1)
,(1111111,5025, 'PM', 2)
,(1111111,2069, 'PM', 3)
,(2222222,5009, 'AM', 1)
,(2222222,6111, 'AM', 1)
,(2222222,7111, 'AM', 1)
SELECT TT.OrderID
, TT.CarNum
, TT.TimeOfDay
, TT.OrderNum
, ROW_NUMBER() OVER (PARTITION BY TT.CarNum ORDER BY CarNum) AS TotalCarNum
, COUNT(TT.CarNum) OVER (PARTITION BY TT.CarNum ORDER BY CarNum) AS TotalCarNum2
FROM #testTable AS TT
ORDER BY TT.OrderID, TT.TimeOfDay, TT.OrderNum
uj5u.com熱心網友回復：
您可以使用dense_rank來模仿 COUNT(DISTINCT) 行為
SELECT TT.OrderID
,TT.CarNum
,TT.TimeOfDay
,TT.OrderNum
,DENSE_RANK() OVER (
PARTITION BY OrderID ORDER BY CarNum
) DENSE_RANK() OVER (
PARTITION BY OrderID ORDER BY CarNum DESC
) - 1 AS TotalCarNum
FROM #testTable AS TT
ORDER BY TT.OrderID
,TT.TimeOfDay
,TT.OrderNum
uj5u.com熱心網友回復：
你需要Subquery 得到你想要的結果
SELECT t.orderid,
t1.carnum,
t.timeofday,
t.ordernum
FROM #testtable T
JOIN (SELECT orderid,
Count(DISTINCT carnum) CarNum
FROM #testtable
GROUP BY orderid) T1
ON T.orderid = T1.orderid
ORDER BY T.orderid,
T.timeofday,
T.ordernum
uj5u.com熱心網友回復：
您可以使用COUNT DISTINCT一個公用表運算式：
;WITH
CNT AS (
SELECT OrderID, COUNT(DISTINCT CarNum) TotalCarNum
FROM #testTable
group by OrderID
)
SELECT TT.OrderID
, TT.CarNum
, TT.TimeOfDay
, TT.OrderNum
, cnt.TotalCarNum
FROM #testTable AS TT
join cnt on cnt.OrderID = tt.OrderID
ORDER BY TT.OrderID, TT.TimeOfDay, TT.OrderNum
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/447365.html
上一篇：根據條件獲取兩個表之間的公共記錄
下一篇：T-SQL簡單轉換