求一分组自动产生序号的SQL语句解决办法
求一分组自动产生序号的SQL语句
表结构如下
col1 col2
a x1
a x2
a x3
b x1
b x2
...
现象通过col1字段分组产生自动排序号如下
id col1 col2
1 a x1
1 a x2
1 a x3
2 b x1
2 b x2
...
请问如何编写SQL？
------解决方案--------------------
表结构如下
col1 col2
a x1
a x2
a x3
b x1
b x2
...
现象通过col1字段分组产生自动排序号如下
id col1 col2
1 a x1
1 a x2
1 a x3
2 b x1
2 b x2
...
请问如何编写SQL？
------解决方案--------------------
- SQL code
--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([col1] varchar(1),[col2] varchar(2))
insert [tbl]
select 'a','x1' union all
select 'a','x2' union all
select 'a','x3' union all
select 'b','x1' union all
select 'b','x2'
select ROW_NUMBER()over(partition by [col2] order by [col1]) as id,
col1,col2 from tbl order by col1,col2
/*
id col1 col2
1 a x1
1 a x2
1 a x3
2 b x1
2 b x2
*/