SQL数据修改

[plutu]

由于当初设计失误，选用了smalldatetime类型，结果记录的时间中没有秒，我按照时间排序时就没法区分两个时间相同的记录，我现在把类型改为了datetime类型，现在想把原来时间完全相同的时间批量修改一下，从相同记录的第二条开始依次增加5秒，比如5条记录的时间都是2011.3.14 14:22:00，怎样从第二条开始逐条变成2011.3.14 14:22:05、2011.3.14 14:22:10、2011.3.14 14:22:15、2011.3.14 14:22:20，因为里边的数据量太大，因此想找个简便的办法，请高手支招，谢谢！

[AcHerat]

--测试库里测试！

--有主键id没？有的话：

create table tb(id int,date datetime)
insert into tb
select 1,'2011-3-2 14:00' union all
select 2,'2011-3-2 14:00' union all
select 3,'2011-3-2 14:00' union all
select 4,'2011-3-2 14:00' union all
select 6,'2011-3-2 14:00' union all
select 7,'2011-3-2 14:00' union all
select 9,'2011-3-2 14:00'
go

update tb
set date = dateadd(ss,(select count(*)*5 from tb t where id <= tb.id),date)

select *
from tb

drop table tb

/*

id          date
----------- -----------------------
1           2011-03-02 14:00:05.000
2           2011-03-02 14:00:10.000
3           2011-03-02 14:00:15.000
4           2011-03-02 14:00:20.000
6           2011-03-02 14:00:25.000
7           2011-03-02 14:00:30.000
9           2011-03-02 14:00:35.000

(7 行受影响)

[王向飞]

;with a as (select * ,row_number()over(order by 时间列) as id from tb)

update a
set 时间列=dateadd(ss,5,时间列) 

[Mr_Nice]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([D_SDateTime] datetime,[D_DateTime] sql_variant)
insert [TB]
select '2011-01-01 13:40:000',null union all
select '2011-01-02 14:20:000',null union all
select '2011-01-01 13:40:000',null union all
select '2011-01-01 13:40:000',null union all
select '2011-01-02 14:20:000',null

select * from [TB]

WITH CTE
AS(
SELECT 
TOP 100 PERCENT D_SDatetime,
num = ROW_NUMBER() OVER ( PARTITION BY D_SDatetime ORDER BY GETDATE())
FROM dbo.TB
ORDER BY D_SDatetime)

SELECT D_SDatetime,D_DateTime = CASE WHEN num = 1 THEN D_SDatetime ELSE DATEADD(ss,(num-1)*5,D_SDatetime) END 
FROM CTE


/*
D_SDatetime             D_DateTime
----------------------- -----------------------
2011-01-01 13:40:00.000 2011-01-01 13:40:00.000
2011-01-01 13:40:00.000 2011-01-01 13:40:05.000
2011-01-01 13:40:00.000 2011-01-01 13:40:10.000
2011-01-02 14:20:00.000 2011-01-02 14:20:00.000
2011-01-02 14:20:00.000 2011-01-02 14:20:05.000

(5 行受影响)*/

LZ参考

[plutu]

create table tb(id int,date datetime)
insert into tb
select 1,'2011-3-2 14:00' union all
select 2,'2011-3-2 14:00' union all
select 3,'2011-3-2 14:00' union all
select 4,'2011-3-2 14:00' union all
select 5,'2011-3-2 14:00' union all
select 6,'2011-3-2 14:00' union all
select 7,'2011-3-2 14:00' union all
select 8,'2011-3-2 15:00' union all
select 9,'2011-3-3 15:00' union all
select 10,'2011-3-3 15:00'
update tb
set date = dateadd(ss,(select count(*)*5 from tb t where id <= tb.id),date)

select *
from tb

drop table tb

id date
----------- ------------------------------------------------------
1 2011-03-02 14:00:05.000
2 2011-03-02 14:00:10.000
3 2011-03-02 14:00:15.000
4 2011-03-02 14:00:20.000
5 2011-03-02 14:00:25.000
6 2011-03-02 14:00:30.000
7 2011-03-02 14:00:35.000
8 2011-03-02 15:00:40.000
9 2011-03-03 15:00:45.000
10 2011-03-03 15:00:50.000

（所影响的行数为 10 行）
其实我想第一条记录不变，另外你看第9条开始又有新的重复了，我要求第九条不变，第10条增加5秒，但楼上的给出的都变了

[plutu]

刚才说的是1#楼的，没想到大家这么热心，我再试一下楼上各位的，谢谢！

[plutu]

环境是SQL Server2000

[王向飞]

update a
set 时间列=dateadd(ss,5*id,时间列) 

[王向飞]

[Quote=引用 6 楼 plutu 的回复:]
环境是SQL Server2000
[/Quote]

[plutu]

继续啊同志们

[bluesmiler]

create table tb(id int,date datetime)
insert into tb
select 1,'2011-3-2 14:00' union all
select 2,'2011-3-2 14:00' union all
select 3,'2011-3-2 14:00' union all
select 4,'2011-3-2 14:00' union all
select 5,'2011-3-2 14:00' union all
select 6,'2011-3-2 14:00' union all
select 7,'2011-3-2 14:00' union all
select 8,'2011-3-2 15:00' union all
select 9,'2011-3-3 15:00' union all
select 10,'2011-3-3 15:00'

update a
set date=dateadd(ss,(select count(*)*5 from tb b where a.date=b.date and b.id < a.id),a.date)
from tb a


1 2011-03-02 14:00:00.000
2 2011-03-02 14:00:05.000
3 2011-03-02 14:00:10.000
4 2011-03-02 14:00:15.000
5 2011-03-02 14:00:20.000
6 2011-03-02 14:00:25.000
7 2011-03-02 14:00:30.000
8 2011-03-02 15:00:00.000
9 2011-03-03 15:00:00.000
10 2011-03-03 15:00:05.000

[feixianxxx]

....给 时间字段做个分组排序
....根据分组序号 为时间字段 dateadd(second,5*序号,时间字段)