为每个类别的n行批设置行号

lrpiutwd 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(417)

我有一张table

+-----+----------+---------+
| id  | name     | phase   |
+-----+----------+---------+
| 101 | Bolt     | PHASE 1 |
| 102 | Nut      | PHASE 1 |
| 103 | Screw    | PHASE 1 |
| 104 | Hex BOLT | PHASE 1 |
| 105 | Rubber   | PHASE 1 |
| 106 | Aluminum | PHASE 2 |
| 107 | Slate    | PHASE 2 |
| 108 | Pen      | PHASE 3 |
| 109 | Pencil   | PHASE 3 |
| 110 | Mouse    | PHASE 3 |
| 111 | Keyboard | PHASE 3 |
+-----+----------+---------+

我想创建另一个列，在其中输入行号。
逻辑：对于3行，它应该是相同的，并更改为第4行的下一个值。当相位改变时，它应该转到下一个数字，即使前一个数字的3集不完整。
预期产量

+-----+----------+---------+-----+
| id  | name     | phase   | SET |
+-----+----------+---------+-----+
| 101 | Bolt     | PHASE 1 | 1   |
| 102 | Nut      | PHASE 1 | 1   |
| 103 | Screw    | PHASE 1 | 1   |
| 104 | Hex BOLT | PHASE 1 | 2   |
| 105 | Rubber   | PHASE 1 | 2   |
| 106 | Aluminum | PHASE 2 | 3   |
| 107 | Slate    | PHASE 2 | 3   |
| 108 | Pen      | PHASE 3 | 4   |
| 109 | Pencil   | PHASE 3 | 4   |
| 110 | Mouse    | PHASE 3 | 4   |
| 111 | Keyboard | PHASE 3 | 5   |
+-----+----------+---------+-----+

我尝试了下面的查询，但它没有给我所需的输出。

select *, (row_number() over (order by phase)-1) / 3 as sets
from table_main

实际输出：

+-----+----------+---------+------+
| id  | name     | phase   | sets |
+-----+----------+---------+------+
| 101 | Bolt     | PHASE 1 | 0    |
| 102 | Nut      | PHASE 1 | 0    |
| 103 | Screw    | PHASE 1 | 0    |
| 104 | Hex BOLT | PHASE 1 | 1    |
| 105 | Rubber   | PHASE 1 | 1    |
| 106 | Aluminum | PHASE 2 | 1    |
| 107 | Slate    | PHASE 2 | 2    |
| 108 | Pen      | PHASE 3 | 2    |
| 109 | Pencil   | PHASE 3 | 2    |
| 110 | Mouse    | PHASE 3 | 3    |
| 111 | Keyboard | PHASE 3 | 3    |
+-----+----------+---------+------+

我试过了 DENSE_RANK() 但没有得到预期的产出。
在这里摆弄

sql sql-server sql-server-2012 row-number

来源：https://stackoverflow.com/questions/62064412/set-row-number-for-batch-of-n-rows-per-category

2条答案

按热度按时间

rbpvctlc1#

试一试：

WITH CTE
as
(
select *, ROW_NUMBER () OVER (PARTITION BY phase ORDER BY id) as rn
from table_main
)
SELECT *,DENSE_RANK() over (ORDER BY phase ,CEILING(rn/3.0)) as set
FROM CTE

赞(0）回复(0）举报 2021-08-13

c3frrgcw2#

使用 LAG 获取的上一行值 column 如果它改变了，那么加上 1 . 使用 ROW_NUMBER() 如前所述 %3 然后 sum 这些值如下所示。

;WITH tm AS (
    SELECT *, 
            IIF((LAG(phase, 1) OVER (ORDER BY phase, id)) = phase, 0, 1) AS phase_change,
            IIF((ROW_NUMBER() OVER (PARTITION BY phase ORDER BY phase)-1)%3 = 0, 1, 0) AS [set]
    FROM table_main
) 
SELECT id, 
        name, 
        phase,
        SUM(IIF([set] > phase_change, [set], phase_change)) OVER (ORDER BY phase, id) AS [set]
FROM tm

赞(0）回复(0）举报 2021-08-13

我来回答

为每个类别的n行批设置行号

2条答案

相关问题

热门标签

最新问答