如何在SQL Server中将分组扩展到row_number

I have the following:

我有以下内容：

DECLARE @TABLE1 TABLE
(
    VAL1 VARCHAR(2),
    VAL2 VARCHAR(2)
)

INSERT INTO @TABLE1 
VALUES ('A', 'X'), ('A', 'Y'), ('A', 'Z'),
       ('B', 'X'), ('B', 'Y'),
       ('C', 'X'), ('D', 'X')

And I want to get the following results:

我希望得到以下结果：

1   A   X
1   A   Y
1   A   Z
2   B   X
2   B   Y
3   C   X
4   D   X

What I did is this:

我做的是这样的：

WITH T1 AS
(
    SELECT 
        ROW_NUMBER() OVER(ORDER BY VAL1) RW,
        VAL1
    FROM 
        @TABLE1
    GROUP BY
        VAL1
)
SELECT 
    T1.RW, T2.VAL1, T2.VAL2 
FROM 
    T1
INNER JOIN 
    @TABLE1 T2 ON T1.VAL1 = T2.VAL1

It works, but I wonder if there is another easier way to do it?

它有效，但我想知道是否有另一种更简单的方法可以做到这一点？

Thanks for your help!

谢谢你的帮助！

1 个解决方案

#1

You can use dense_rank():

你可以使用dense_rank（）：

select dense_rank() over (order by val1) as rw, val1, val2
from @table1 t1;