34,576
社区成员
发帖
与我相关
我的任务
分享
;with tb(列1,列2) as(
select 'a',1 union all
select 'a',1 union all
select'b',1 union all
select 'b',1 union all
select 'c',2 union all
select 'c',2 union all
select 'd',2 union all
select 'd',2 union all
select 'e',2 union all
select 'e',2
)
select * from tb
列1 列2
a 1
a 1
b 1
b 1
c 2
c 2
d 2
d 2
e 2
e 2
列1 列2 编号
a 1 1
a 1 1
b 1 2
b 1 2
c 2 3
c 2 3
d 2 4
d 2 4
e 2 5
e 2 5
;with tb(列1,列2) as(
select 'e',1 union all
select 'e',1 union all
select'b',1 union all
select 'b',1 union all
select 'c',2 union all
select 'c',2 union all
select 'a',2 union all
select 'a',2 union all
select 'e',2 union all
select 'e',2
)
select * from tb
;with tb(列1,列2) as(
select 'a',1 union all
select 'a',1 union all
select'b',1 union all
select 'b',1 union all
select 'c',2 union all
select 'c',2 union all
select 'd',2 union all
select 'd',2 union all
select 'e',2 union all
select 'e',2
)
SELECT
a.*
,b.rid AS [序号]
from tb AS a INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY 列1) as rid,列1 FROM tb GROUP BY 列1
) AS b ON a.列1=b.列1
/*
列1 列2 序号
---- ----------- --------------------
a 1 1
a 1 1
b 1 2
b 1 2
c 2 3
c 2 3
d 2 4
d 2 4
e 2 5
e 2 5
*/
--测试数据
;with tb(列1,列2) as(
select 'a',1 union all
select 'a',1 union all
select'b',1 union all
select 'b',1 union all
select 'c',2 union all
select 'c',2 union all
select 'd',2 union all
select 'd',2 union all
select 'e',2 union all
select 'e',2
)
select *,DENSE_RANK() OVER ( ORDER BY 列1, 列2 ) 编号 from tb