sql 怎么添加序号
sql 如何添加序号
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如何添加序号??请教各位大虾,在此十分感谢
------解决方案--------------------
哦,前一帖子里不是用了序号了嘛!
呵呵,我忘了我是不用序号来解的.
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' go select 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.000 2 2 A 2011-10-15 00:00:00.000 2011-10-31 00:00:00.000 3 3 A 2011-12-16 00:00:00.000 2011-12-30 00:00:00.000 1 4 B 2011-09-10 00:00:00.000 2011-09-15 00:00:00.000 2 5 B 2011-11-10 00:00:00.000 2011-11-20 00:00:00.000 1 6 C 2011-12-01 00:00:00.000 2011-12-03 00:00:00.000 2 7 C 2011-12-05 00:00:00.000 2011-12-06 00:00:00.000 (7 行受影响) */ go drop table tb
------解决方案--------------------
- SQL code
新增列时 --> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([单号] int,[条码] nvarchar(1),[送修时间] Datetime,[维修完成时间] Datetime) Insert #T select 1,N'A','2011-10-9','2011-10-10' union all select 2,N'A','2011-10-15','2011-10-31' union all select 3,N'A','2011-12-16','2011-12-30' union all select 4,N'B','2011-9-10','2011-9-15' union all select 5,N'B','2011-11-10','2011-11-20' union all select 6,N'C','2011-12-1','2011-12-3' union all select 7,N'C','2011-12-5','2011-12-6' Go alter table #T add 序号 int go update t set 序号=(select count(1) from #T where 条码=t.条码 and 单号<=t.单号) from #T as t --sql2005用 update t set 序号=ID from (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.000 2 2 A 2011-10-15 00:00:00.000 2011-10-31 00:00:00.000 3 3 A 2011-12-16 00:00:00.000 2011-12-30 00:00:00.000 1 4 B 2011-09-10 00:00:00.000 2011-09-15 00:00:00.000 2 5 B 2011-11-10 00:00:00.000 2011-11-20 00:00:00.000 1 6 C 2011-12-01 00:00:00.000 2011-12-03 00:00:00.000 2 7 C 2011-12-05 00:00:00.000 2011-12-06 00:00:00.000 */