sql - SQL Server 每组添加行号

Question

我正在处理 SQL Server 2016 的查询。我按serial_no排序并按 pay_type 分组，我想在下面添加行号相同的示例

row_no | pay_type | serial_no
   1   |    A     | 4000118445
   2   |    A     | 4000118458
   3   |    A     | 4000118461
   4   |    A     | 4000118473
   5   |    A     | 4000118486
   1   |    B     | 4000118499
   2   |    B     | 4000118506
   3   |    B     | 4000118519
   4   |    B     | 4000118521
   1   |    A     | 4000118534
   2   |    A     | 4000118547
   3   |    A     | 4000118550
   1   |    B     | 4000118562
   2   |    B     | 4000118565
   3   |    B     | 4000118570
   4   |    B     | 4000118572

请帮帮我..

标签： sqlsql-server-2016

Answer 1

SELECT
    ROW_NUMBER() OVER(PARTITION BY paytype ORDER BY serial_no) as row_no,
    paytype, serial_no
FROM table
ORDER BY serial_no