表结构如下
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 allselect 'a','x2' union allselect 'a','x3' union allselect 'b','x1' union allselect 'b','x2'select ROW_NUMBER()over(partition by [col2] order by [col1]) as id, col1,col2 from tbl order by col1,col2/*id col1 col21 a x11 a x21 a x32 b x12 b x2*/