求答案:数据分组显示,增加递增编号解决方法
求答案:数据分组显示,增加递增编号
数据如下:
NO 顺序
000000000001 1
000000000002 1
000000000003 1
010000000001 5
010000000001 4
010000000001 2
010000000001 1
010000000002 4
010000000002 3
010000000002 2
010000000002 1
想得到的结果:
NO 顺序 编号
000000000001 1 1
000000000002 1 1
000000000003 1 1
010000000001 5 1
010000000001 4 2
010000000001 2 3
010000000001 1 4
010000000002 4 1
010000000002 3 2
010000000002 2 3
010000000002 1 4
有一点点思路,可是不能实现,请各位帮忙看看,谢谢。
------解决方案--------------------
数据如下:
NO 顺序
000000000001 1
000000000002 1
000000000003 1
010000000001 5
010000000001 4
010000000001 2
010000000001 1
010000000002 4
010000000002 3
010000000002 2
010000000002 1
想得到的结果:
NO 顺序 编号
000000000001 1 1
000000000002 1 1
000000000003 1 1
010000000001 5 1
010000000001 4 2
010000000001 2 3
010000000001 1 4
010000000002 4 1
010000000002 3 2
010000000002 2 3
010000000002 1 4
有一点点思路,可是不能实现,请各位帮忙看看,谢谢。
------解决方案--------------------
- SQL code
SELECT *,编号=(SELECT COUNT(*) FROM TB WHERE NO=T.NO AND 顺序<=T.顺序) FROM TB T
------解决方案--------------------
- SQL code
select *,编号=row_number over(partition by NO order by 顺序) from tb
------解决方案--------------------
- SQL code
--sql 2000 select * , 编号 = (select count(1) from tb where NO = t.NO and 顺序 < t.顺序) + 1 from tb t --sql 2005 select * , 编号 = row_number() over(partition by NO order by 顺序) from tb
------解决方案--------------------
------解决方案--------------------
- SQL code
create table test(NO varchar(20),顺序 int) go insert test select '000000000001' , 1 insert test select '000000000002' , 1 insert test select '000000000003' , 1 insert test select '010000000001' , 5 insert test select '010000000001' , 4 insert test select '010000000001' , 2 insert test select '010000000001' , 1 insert test select '010000000002' , 4 insert test select '010000000002' , 3 insert test select '010000000002' , 2 insert test select '010000000002' , 1 go select * ,num=identity(int,1,1) into #temp from test go select no,顺序, (select count(1) from #temp where a.no=no and num<=a.num) 编号 from #temp a go drop table #temp,test go /* no 顺序 编号 -------------------- ----------- ----------- 000000000001 1 1 000000000002 1 1 000000000003 1 1 010000000001 5 1 010000000001 4 2 010000000001 2 3 010000000001 1 4 010000000002 4 1 010000000002 3 2 010000000002 2 3 010000000002 1 4 (所影响的行数为 11 行) */