我试图将排名应用于我的数据集,其逻辑如下:
对于每个ID,按ID2 ASC排序和按IsMaster Desc排序对行1进行排序,并且仅在ID4值更改时才对其进行更改
我的数据集和所需的输出如下所示:
测试数据
CREATE TABLE Test_Table
(ID INT ,ID2 INT, IsMaster INT, ID4 VARCHAR(10))
GO
INSERT INTO Test_Table (ID ,ID2 , IsMaster , ID4 )
VALUES
(1, 101, 1 ,'AAA') -- 1 <-- Desired output for rank
,(1, 102, 0 ,'AAA') -- 1
,(1, 103, 0 ,'AAB') -- 2
,(1, 104, 0 ,'AAB') -- 2
,(1, 105, 0 ,'CCC') -- 3
,(2, 101, 1 ,'AAA') -- 1
,(2, 102, 0 ,'AAA') -- 1
,(2, 103, 0 ,'AAA') -- 1
,(2, 104, 0 ,'AAB') -- 2
,(2, 105, 0 ,'CCC') -- 3
到目前为止,这是我尝试过的:
SELECT *
,DENSE_RANK() OVER (PARTITION BY ID ORDER BY ID2 ASC, IsMaster DESC ) rn
FROM Test_Table
请请帮助我,谢谢。
另一种方法可能效率较低,但它将起作用。
WITH X AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID2) RowNum
FROM dbo.Test_Table
)
, CTE_VehicleNumber
as
(
SELECT T.ID , T.ID2, t.IsMaster ,T.ID4 , t.RowNum , 1 as [Rank]
FROM X as T
WHERE T.IsMaster = 1
UNION ALL
SELECT T.ID, T.ID2, t.IsMaster ,T.ID4 , t.RowNum , CASE WHEN t.ID4 <> c.ID4 THEN 1+ C.[Rank]
ELSE 0+ C.[Rank]
END as [Rank]
FROM CTE_VehicleNumber as C
inner join X as T ON T.RowNum = C.RowNum + 1
AND t.ID = c.ID
)
SELECT ID , ID2, IsMaster ,ID4 , [Rank]
FROM CTE_VehicleNumber
ORDER BY ID , ID2, IsMaster ,ID4 , [Rank]
OPTION (MAXRECURSION 0);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句