sql 如何添加序号

[so_fantasy]

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如何添加序号??请教各位大虾,在此十分感谢

[koumingjie]

;with cte as
(序号=row_number()over(partition by 条码 order by 单号),* from A)

select * from cte

[koumingjie]

;with cte as
(序号=row_number()over(partition by 条码 order by 单号),* from A)

select * from cte

[中国风]

新增列还是查询?
05时用1楼方法

SQL2000可用

select ID=(select count(1) from A where 条码=t.条码 and 单号<=t.单号)
,*
from A as t

[中国风]

--> --> (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
select ID=(select count(1) from #T where 条码=t.条码 and 单号<=t.单号) 
,* 
from #T as t

--sql2005用
select ID=row_number()over(partition by 条码 order by 单号),* from #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
*/

[-晴天]

哦,前一帖子里不是用了序号了嘛!

呵呵,我忘了我是不用序号来解的.

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

不过,上帖的要求,用序号解还是有点问题的.

[中国风]

新增列时

--> --> (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
*/

[zxqzxq111]

对条码类型分组，然后按单号排序