根据 ID 在其自己的列中获取唯一数据值的总数？

Question

如何获取每个 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

Answer 1

您可以使用 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

Answer 2

您需要子查询来获得您想要的结果

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  

Answer 3

您可以使用 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