A表如下:
单号 条码 送修时间 维修完成时间
1 A 2011-10-9 2011-10-10
2 A 2011-10-15 2011-10-31
3 A 2011-12-16 2011-12-30
4 B 2011-9-10 2011-9-15
5 B 2011-11-10 2011-11-20
6 C 2011-12-1 2011-12-3
7 C 2011-12-5 2011-12-6
效果如下:
序号 单号 条码 送修时间 维修完成时间
1 1 A 2011-10-9 2011-10-10
2 2 A 2011-10-15 2011-10-31
3 3 A 2011-12-16 2011-12-30
1 4 B 2011-9-10 2011-9-15
2 5 B 2011-11-10 2011-11-20
1 6 C 2011-12-1 2011-12-3
2 7 C 2011-12-5 2011-12-6
用sql如何添加序号??请教各位大虾,在此十分感谢
------解决方案--------------------
哦,前一帖子里不是用了序号了嘛!
呵呵,我忘了我是不用序号来解的.
- SQL code
create table tb(单号 int,条码 varchar(10),送修时间 datetime,维修完成时间 datetime)insert into tb select 1,'A','2011-10-9','2011-10-10'insert into tb select 2,'A','2011-10-15','2011-10-31'insert into tb select 3,'A','2011-12-16','2011-12-30'insert into tb select 4,'B','2011-9-10','2011-9-15'insert into tb select 5,'B','2011-11-10','2011-11-20'insert into tb select 6,'C','2011-12-1','2011-12-3'insert into tb select 7,'C','2011-12-5','2011-12-6'goselect row_number()over(partition by 条码 order by 送修时间)序号,* from tb/*序号 单号 条码 送修时间 维修完成时间-------------------- ----------- ---------- ----------------------- -----------------------1 1 A 2011-10-09 00:00:00.000 2011-10-10 00:00:00.0002 2 A 2011-10-15 00:00:00.000 2011-10-31 00:00:00.0003 3 A 2011-12-16 00:00:00.000 2011-12-30 00:00:00.0001 4 B 2011-09-10 00:00:00.000 2011-09-15 00:00:00.0002 5 B 2011-11-10 00:00:00.000 2011-11-20 00:00:00.0001 6 C 2011-12-01 00:00:00.000 2011-12-03 00:00:00.0002 7 C 2011-12-05 00:00:00.000 2011-12-06 00:00:00.000(7 行受影响)*/godrop table tb
------解决方案--------------------
- SQL code
新增列时--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null drop table #TGoCreate table #T([单号] int,[条码] nvarchar(1),[送修时间] Datetime,[维修完成时间] Datetime)Insert #Tselect 1,N'A','2011-10-9','2011-10-10' union allselect 2,N'A','2011-10-15','2011-10-31' union allselect 3,N'A','2011-12-16','2011-12-30' union allselect 4,N'B','2011-9-10','2011-9-15' union allselect 5,N'B','2011-11-10','2011-11-20' union allselect 6,N'C','2011-12-1','2011-12-3' union allselect 7,N'C','2011-12-5','2011-12-6'Goalter table #T add 序号 intgoupdate tset 序号=(select count(1) from #T where 条码=t.条码 and 单号<=t.单号) from #T as t--sql2005用update tset 序号=IDfrom (select ID=row_number()over(partition by 条码 order by 单号),* from #T)t/*ID 单号 条码 送修时间 维修完成时间1 1 A 2011-10-09 00:00:00.000 2011-10-10 00:00:00.0002 2 A 2011-10-15 00:00:00.000 2011-10-31 00:00:00.0003 3 A 2011-12-16 00:00:00.000 2011-12-30 00:00:00.0001 4 B 2011-09-10 00:00:00.000 2011-09-15 00:00:00.0002 5 B 2011-11-10 00:00:00.000 2011-11-20 00:00:00.0001 6 C 2011-12-01 00:00:00.000 2011-12-03 00:00:00.0002 7 C 2011-12-05 00:00:00.000 2011-12-06 00:00:00.000*/