SQL 按ID及列内容自动生成序号排序有关问题

SQL 按ID及列内容自动生成序号排序问题


ID 列 内容

1 A 100
2 A 200
3 C 300
4 B 300
5 A 300
6 C 200

要求显示如下结果：

ID 列 内容 生成的序号
1 A 100 1
2 A 200 2
5 A 300 3
4 B 300 1
3 C 300 1
6 C 200 2



[解决办法]
select *,生成的序号=row_number() over(partition by 列 order by 内容)
from tb
[解决办法]

SQL code

--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([ID] int,[列] varchar(1),[内容] int)insert [tb]select 1,'A',100 union allselect 2,'A',200 union allselect 3,'C',300 union allselect 4,'B',300 union allselect 5,'A',300 union allselect 6,'C',200--------------开始查询----------------------------2005/2008select *,序号=row_number() over(partition by [列] order by id) from [tb]--2000select *,序号=(select count(1) from tb where [列]=t.[列] and id<=t.id) from [tb] t order by 列,序号----------------结果----------------------------/* ID          列    内容          序号----------- ---- ----------- --------------------1           A    100         12           A    200         25           A    300         34           B    300         13           C    300         16           C    200         2(6 行受影响)*/
[解决办法]
SQL code
select *,序号=row_number() over(partition by 列 order by 内容)from tb