Dense_Rank排序 [英] Dense_Rank ordering

问题描述

我已经阅读了该问题的一些变体，但解决方案似乎无效.

I've read a few variations of this question, but the solutions don't seem to be working.

我希望为每个"OrderNo"&动态创建一个子组" "GroupID".子组应按"OrderLine"排序，例如:(预期结果)

I wish to dynamically create a "Subgroup" for each "OrderNo" & "GroupID". Subgroups should be ordered by "OrderLine" eg: (Expected outcome)

OrderNo OrderLine   GroupID Subgroup
------------------------------------
10463   1            798    1
10463   2            799    2
10463   3            797    3
10463   5            65     4
10463   6            65     4
10463   7            65     4
10481   4            917    1
10481   5            918    2
10481   6            131    3
10481   7            131    3
10481   8            131    3
10481   9            130    4

我已经使用Dense_Rank()在下面创建了正确的组，但是排序(和排名)完全不正确.

I've used Dense_Rank() to create the correct groups below but the ordering(and rank) is totally incorrect.

SELECT 
    OrderNo, OrderLine, GroupID,
    DENSE_RANK() OVER (PARTITION BY OrderNo ORDER BY GroupID) AS Subgroup
FROM 
    #temptable
ORDER BY
    OrderNo, OrderLine;

输出:

OrderNo OrderLine   GroupID Subgroup
------------------------------------
10463   1            798    3
10463   2            799    4
10463   3            797    2
10463   5            65     1
10463   6            65     1
10463   7            65     1
10481   4            917    3
10481   5            918    4
10481   6            131    2
10481   7            131    2
10481   8            131    2
10481   9            130    1

查询:

-- Temp tables
CREATE TABLE #temptable
(  
    OrderNo varchar(5),  
    OrderLine int, 
    GroupID int
); 

INSERT INTO #temptable (OrderNo, OrderLine, GroupID)
VALUES ('10463', '1', '798'), ('10463', '2', '799'),
       ('10463', '3', '797'), ('10463', '5', '65'),
       ('10463', '6', '65'), ('10463', '7', '65'),
       ('10481', '4', '917'), ('10481', '5', '918'),
       ('10481', '6', '131'), ('10481', '7', '131'),
       ('10481', '8', '131'), ('10481', '9', '130');

推荐答案

您要按OrderLine排序DENSE_RANK分区，但是如果有多个记录具有相同的GroupID，则需要排名是相同的.一种选择是使用子查询来为具有相同GroupID的一组记录标识为OrderLine分配单个值(即最小值).然后可以将此表重新连接到您的#temptable，并且可以根据需要将有效的OrderLine用作DENSE_RANK.

You want to order the DENSE_RANK partition by the OrderLine, but in cases where more than one record has the same GroupID, you want the rank to be the same. One option is to use a subquery to identify assign a single value for the OrderLine (say the minimum) for a set of records which have the same GroupID. This table can then be joined back to your #temptable, and the effective OrderLine can be used to DENSE_RANK as you want.

SELECT t1.OrderNo,
       t1.OrderLine,
       t1.GroupID,
       DENSE_RANK() OVER (PARTITION BY t1.OrderNo ORDER BY t2.OrderLine) AS Subgroup
FROM #temptable t1
INNER JOIN
(
    SELECT OrderNo,
           MIN(OrderLine) AS OrderLine,
           GroupID
    FROM #temptable
    GROUP BY OrderNo,
             GroupID
) t2
    ON t1.OrderNo = t2.OrderNo AND
       t1.GroupID = t2.GroupID

这篇关于Dense_Rank排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！