SQL Row_Number（）（分区由...排序依据...）IGNORES命令语句

Question

我找不到一个函数来支持我正在尝试的方法。

让我们假设我们有如下表，包含一个字段排序顺序和一定的重复

 
+----------+----------+-----+-------------+-----------+ 
| UniqueId | Id | Qty | RetailPrice | SortOrder | 
+----------+----------+-----+-------------+-----------+ 
|  3124 | 92361725 | 25 |  269.99 |   1 | 
|  2627 | 92361725 | 25 |  269.99 |   2 | 
|  7635 | 92361725 | 25 |  269.99 |   3 | 
|  8732 | 92361725 | 25 |  269.99 |   4 | 
|  3791 | 92361725 | 20 |  269.99 |   5 | 
|  4328 | 92361725 | 25 |  269.99 |   6 | 
+----------+----------+-----+-------------+-----------+ 

我要列举我行增加其价值，当副本被发现，如果没有则重置行号。结果必须在列中显示RN下表，如果数量是评价柱：

 
+----------+----------+-----+-------------+-----------+----+ 
| UniqueId | Id | Qty | RetailPrice | SortOrder | rn | 
+----------+----------+-----+-------------+-----------+----+ 
|  3124 | 92361725 | 25 |  269.99 |   1 | 1 | 
|  2627 | 92361725 | 25 |  269.99 |   2 | 2 | 
|  7635 | 92361725 | 25 |  269.99 |   3 | 3 | 
|  8732 | 92361725 | 25 |  269.99 |   4 | 4 | 
|  3791 | 92361725 | 20 |  269.99 |   5 | 1 | 
|  4328 | 92361725 | 25 |  269.99 |   6 | 1 | 
+----------+----------+-----+-------------+-----------+----+ 

我试图用ROW_NUMBER（）函数，但我不能让我的结果想

;WITH Table1 AS(
SELECT 3124 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 1 SortOrder UNION ALL 
SELECT 2627 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 2 SortOrder UNION ALL 
SELECT 7635 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 3 SortOrder UNION ALL 
SELECT 8732 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 4 SortOrder UNION ALL 
SELECT 3791 UniqueId,92361725 Id, 20 Qty, 269.99 RetailPrice, 5 SortOrder UNION ALL 
SELECT 4328 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 6 SortOrder 
) 

SELECT UniqueId, Id, Qty, RetailPrice, SortOrder, 
ROW_NUMBER() OVER (PARTITION BY Qty ORDER BY SortOrder) rn 
FROM Table1 

 
+----------+----------+-----+-------------+-----------+----+ 
| UniqueId | Id | Qty | RetailPrice | SortOrder | rn | 
+----------+----------+-----+-------------+-----------+----+ 
|  3791 | 92361725 | 20 |  269.99 |   5 | 1 | 
|  3124 | 92361725 | 25 |  269.99 |   1 | 1 | 
|  2627 | 92361725 | 25 |  269.99 |   2 | 2 | 
|  7635 | 92361725 | 25 |  269.99 |   3 | 3 | 
|  8732 | 92361725 | 25 |  269.99 |   4 | 4 | 
|  4328 | 92361725 | 25 |  269.99 |   6 | 5 | 
+----------+----------+-----+-------------+-----------+----+ 

在ORDER BY完全被忽略，任何人都可以帮忙吗？

Answer 1

你在这里。由于您是在2008年，我通过在SortOrder +/- 1上自行加入此表来复制Lead和Lag。我还更新了您的样本组，以便为​​数量为25的新岛屿进行了更新。

对不起，我有更新您的样品组以添加2行到3号岛，并创建2个CTE以获取岛范围。

--Updates Sample Set with 3 Islands. 
WITH Table1 AS(
SELECT 3124 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 1 SortOrder UNION ALL --Island 1 
SELECT 2627 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 2 SortOrder UNION ALL --Island 1 
SELECT 7635 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 3 SortOrder UNION ALL --Island 1 
SELECT 8732 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 4 SortOrder UNION ALL --Island 1 
SELECT 3791 UniqueId,92361725 Id, 20 Qty, 269.99 RetailPrice, 5 SortOrder UNION ALL --Island 2 
SELECT 4328 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 6 SortOrder UNION ALL --Island 3 
SELECT 4328 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 7 SortOrder UNION ALL --Island 3 
SELECT 4328 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 8 SortOrder   --Island 3 
), 

--Creating a CTE to get the Lead and Lag since this is 2008. This will allow us to determine if a row is the first or last row of an island. 
LeadLagTable AS(
SELECT 
    Table1.UniqueId, 
    Table1.Id, 
    Table1.Qty, 
    Table1.RetailPrice, 
    Table1.SortOrder, 
    LeadTable.SortOrder AS LeadSortOrder, 
    LagTable.SortOrder AS LagSortOrder, 
    CASE 
     WHEN LagTable.SortOrder IS NULL THEN 1 
     ELSE 0 
     END AS StartRowFlag, 
    CASE 
     WHEN LeadTable.SortOrder IS NULL THEN 1 
     ELSE 0 
     END AS LastRowFlag 
FROM Table1 
LEFT JOIN Table1 LeadTable ON 
    Table1.SortOrder = LeadTable.SortOrder - 1 
    AND Table1.Qty = LeadTable.Qty 
LEFT JOIN Table1 LagTable ON 
    Table1.SortOrder = LagTable.SortOrder + 1 
    AND Table1.Qty = LagTable.Qty 
), 

--With the LeadLagTable we can now get the ranges for each island, as well as a unique ID for each island. 
Ranges AS (
SELECT 
    RangeStart, 
    RangeEnd, 
    ROW_NUMBER() OVER (ORDER BY RangeStart) AS RangeRowNum 
FROM (
    SELECT 
     StartRow.SortOrder AS RangeStart, 
     EndRow.SortOrder RangeEnd, 
     ROW_NUMBER() OVER (PARTITION BY StartRow.SortOrder ORDER BY EndRow.SortOrder) AS rn 
    FROM LeadLagTable StartRow 
    JOIN LeadLagTable EndRow ON 
     StartRow.StartRowFlag = 1 
     AND EndRow.LastRowFlag = 1 
     AND StartRow.SortOrder <= EndRow.SortOrder 
     AND StartRow.Qty = EndRow.Qty 
    ) tbl 
WHERE rn = 1 
) 

这里是实际的查询。

--We now join on the island ranges, and partition by the Island ID. 
SELECT 
    UniqueId, 
    Id, 
    Qty, 
    RetailPrice, 
    SortOrder, 
    ROW_NUMBER() OVER (PARTITION BY RangeRowNum ORDER BY SortOrder) AS rn 
FROM Table1 
LEFT JOIN Ranges ON 
    Table1.SortOrder >= Ranges.RangeStart 
    AND Table1.SortOrder <= Ranges.RangeEnd