写一条SQL语句,将表S_INFO中记录按S_ID_NUMBER从小到大排序更新S_TIME字段,
更新方式为以“20071112 13:01:30”开始,按3秒递增。结果示例如下:
小张 100 11-12-2007 13:01:33
小星 101 11-12-2007 13:01:36
小焦 102 11-12-2007 13:01:39
小小 103 11-12-2007 13:01:42
小李 201 11-12-2007 13:01:45
小何 2,022 11-12-2007 13:01:48--建立表:
CREATE TABLE S_INFO /* 学生信息表 */
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM'); 我一条语句更新结果是时间多更新为11-12-2007 13:01:33
还不谈按照3递增更新,我都不是按照S_ID_NUMBER这个字段更新的。
有点古怪这个题目!帮帮忙!谢谢了!
更新方式为以“20071112 13:01:30”开始,按3秒递增。结果示例如下:
小张 100 11-12-2007 13:01:33
小星 101 11-12-2007 13:01:36
小焦 102 11-12-2007 13:01:39
小小 103 11-12-2007 13:01:42
小李 201 11-12-2007 13:01:45
小何 2,022 11-12-2007 13:01:48--建立表:
CREATE TABLE S_INFO /* 学生信息表 */
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM'); 我一条语句更新结果是时间多更新为11-12-2007 13:01:33
还不谈按照3递增更新,我都不是按照S_ID_NUMBER这个字段更新的。
有点古怪这个题目!帮帮忙!谢谢了!
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM')select * from S_INFOupdate tb
set S_TIME=dateadd(second,3,(select min(S_TIME) from S_INFO where S_TIME>=tb.S_TIME))
from S_INFO tbselect * from S_INFOdrop table S_InFO/*
S_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小张 0 100 2007-11-12 13:01:33.000
小星 1 101 2007-11-12 13:01:36.000
小焦 2 102 2007-11-12 13:01:39.000
小李 1 201 2007-11-12 13:01:45.000
小何 2 2022 2007-11-12 13:01:48.000
小小 3 103 2007-11-12 13:01:42.000(所影响的行数为 6 行)
(所影响的行数为 6 行)S_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小张 0 100 2007-11-12 13:01:36.000
小星 1 101 2007-11-12 13:01:39.000
小焦 2 102 2007-11-12 13:01:42.000
小李 1 201 2007-11-12 13:01:48.000
小何 2 2022 2007-11-12 13:01:51.000
小小 3 103 2007-11-12 13:01:45.000(所影响的行数为 6 行)
*/
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM')
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM')select S_NAME,S_TIME from S_INFOupdate tb
set S_TIME=dateadd(second,3,(select min(S_TIME) from S_INFO where S_TIME>=tb.S_TIME))
from S_INFO tbselect S_NAME,S_TIME from S_INFOdrop table S_InFO/*
--更新前
S_NAME S_TIME
-------------------------------- ------------------------------------------------------
小张 2007-11-12 13:01:33.000
小星 2007-11-12 13:01:36.000
小焦 2007-11-12 13:01:39.000
小李 2007-11-12 13:01:45.000
小何 2007-11-12 13:01:48.000
小小 2007-11-12 13:01:42.000(所影响的行数为 6 行)
(所影响的行数为 6 行)
--更新后
S_NAME S_TIME
-------------------------------- ------------------------------------------------------
小张 2007-11-12 13:01:36.000
小星 2007-11-12 13:01:39.000
小焦 2007-11-12 13:01:42.000
小李 2007-11-12 13:01:48.000
小何 2007-11-12 13:01:51.000
小小 2007-11-12 13:01:45.000(所影响的行数为 6 行)
*/
set @date='2007-11-12 13:01:27'--定义27
update S_INFO
set S_TIME=@date,@date=dateadd(ss,3,@date)
select *,px=identity(int,0,3) into # from S_INFOupdate S_INFO set S_TIME=dateadd(mi,b.px,S_TIME) from S_INFO a
inner join # b on a.S_ID=b.S_ID
declare @date datetime,@date2 datetime
set @date='2007-11-12 13:01:30'--
update S_INFO
set @date2=@date,S_TIME=@date2,@date=dateadd(ss,3,@date)
CREATE TABLE S_INFO /* 学生信息表 */
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM'); declare @date datetime,@date2 datetime
set @date='2007-11-12 13:01:30'--定义27
update S_INFO
set @date2=@date,S_TIME=@date2,@date=dateadd(ss,3,@date)
select * from S_INFOS_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小张 0 100 2007-11-12 13:01:30.000
小星 1 101 2007-11-12 13:01:33.000
小焦 2 102 2007-11-12 13:01:36.000
小李 1 201 2007-11-12 13:01:39.000
小何 2 2022 2007-11-12 13:01:42.000
小小 3 103 2007-11-12 13:01:45.000(所影响的行数为 6 行)
我的正好满足了lz的要求.结帖吧
给我100分
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM'); goupdate a
set S_TIME = dateadd(s,3*b.px,'20071112 13:01:30')
from S_INFO a left join
(select * ,px = isnull((select count(1) from s_info where s_time <= c.s_time),0) from S_INFO c) b
on checksum(a.S_NAME,a.S_ID,a.S_ID_NUMBER,a.S_TIME)= checksum(b.S_NAME,b.S_ID,b.S_ID_NUMBER,b.S_TIME)
select * from S_INFOdrop table S_INFO/*S_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小张 0 100 2007-11-12 13:01:33.000
小星 1 101 2007-11-12 13:01:36.000
小焦 2 102 2007-11-12 13:01:39.000
小李 1 201 2007-11-12 13:01:45.000
小何 2 2022 2007-11-12 13:01:48.000
小小 3 103 2007-11-12 13:01:42.000(所影响的行数为 6 行)*/
或用一个变量初始-3秒..S_TIME --递增3秒
小梁
等 级:
发表于:2008-01-29 22:44:229楼 得分:0
lz是用一条update
我的正好满足了lz的要求. 结帖吧
给我100分
---楼主本来的数据不是你的那样的,换数据后你的不对了怎么的,也应该是我们各50 吧
select id=identity(int,0,1),* into #tmp
from S_INFO
order by S_ID_NUMBERselect * from #tmpbegin tran
update #tmp set S_TIME=dateadd(s,id*3,(select S_TIME from #tmp where id=0))
commit tran
update tb
set S_TIME=dateadd(second,3*(select count(1) from S_INFO where S_ID_NUMBER<=tb.S_ID_NUMBER),'2007-11-12 13:01:30')
from S_INFO tb方法2:(中国风的方法)
declare @date datetime,@date2 datetime
set @date='2007-11-12 13:01:30'--定义27
update S_INFO
set @date2=@date,S_TIME=@date2,@date=dateadd(ss,3,@date)--不过这有个要求,就是标的原纪录必须按照S_ID_NUMBER从小到大,所以在执行前需要加按照S_ID_NUMBER排序的聚集索引
set S_TIME = dateadd(s,3*b.px,'20071112 13:01:30')
只要修改这个时间 就行
而且一定要按照S_ID_NUMBER从大到小排序更新
一定是一条语句
结果如下:
小何 2,022 11-12-2007 13:01:35
小李 201 11-12-2007 13:01:40
小小 103 11-12-2007 13:01:45
小焦 102 11-12-2007 13:01:50
小星 101 11-12-2007 13:01:55
小张 100 11-12-2007 13:02:00
分不够的话我会再补给大家!谢谢大家支持!
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM'); goupdate a
set S_TIME = dateadd(s,5*b.px,'20071112 13:01:30')
from S_INFO a left join
(select top 100 percent * ,px = isnull((select count(1) from s_info where s_time <= c.s_time),0) from S_INFO c order by s_id_number ) b
on checksum(a.S_NAME,a.S_ID,a.S_ID_NUMBER,a.S_TIME)= checksum(b.S_NAME,b.S_ID,b.S_ID_NUMBER,b.S_TIME)
select * from S_INFO order by s_id_numberdrop table S_INFO /*
S_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小张 0 100 2007-11-12 13:01:35.000
小星 1 101 2007-11-12 13:01:40.000
小焦 2 102 2007-11-12 13:01:45.000
小小 3 103 2007-11-12 13:01:50.000
小李 1 201 2007-11-12 13:01:55.000
小何 2 2022 2007-11-12 13:02:00.000
*/
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM'); goupdate a
set S_TIME = dateadd(s,5*b.px,'20071112 13:01:30')
from S_INFO a left join
(select top 100 percent * ,px = isnull((select count(1) from s_info where s_time <= c.s_time),0) from S_INFO c order by s_id_number desc ) b
on checksum(a.S_NAME,a.S_ID,a.S_ID_NUMBER,a.S_TIME)= checksum(b.S_NAME,b.S_ID,b.S_ID_NUMBER,b.S_TIME)
select * from S_INFO order by s_id_number descdrop table S_INFO /*
S_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小何 2 2022 2007-11-12 13:02:00.000
小李 1 201 2007-11-12 13:01:55.000
小小 3 103 2007-11-12 13:01:50.000
小焦 2 102 2007-11-12 13:01:45.000
小星 1 101 2007-11-12 13:01:40.000
小张 0 100 2007-11-12 13:01:35.000*/
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:
INSERT INTO @S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO @S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO @S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO @S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO @S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO @S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM');
--上面是测试数据,下面就是你要的一条UPDATE 语句,其中的5*b.c中的5你可以换成你要的数字update @s_info set s_time = dateadd(second,5*b.c,'11/12/2007 01:01:30 PM' )
from @s_info a,
( select s_id_number,(select count(*)+1 from @s_info where s_id_number > c.s_id_number) as c from @s_info c) b
where a.s_id_number = b.s_id_number
--检查结果
select * from @s_info order by s_id_number desc/* ^_^,一不小心结果就正确
S_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小何 2 2022 2007-11-12 13:01:35.000
小李 1 201 2007-11-12 13:01:40.000
小小 3 103 2007-11-12 13:01:45.000
小焦 2 102 2007-11-12 13:01:50.000
小星 1 101 2007-11-12 13:01:55.000
小张 0 100 2007-11-12 13:02:00.000
*/
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM'); goupdate a
set S_TIME = dateadd(s,5*b.px,'20071112 13:01:30')
from S_INFO a left join
(select top 100 percent * ,px = isnull((select count(1) from s_info where s_time >= c.s_time),0) from S_INFO c order by s_id_number desc ) b
on checksum(a.S_NAME,a.S_ID,a.S_ID_NUMBER,a.S_TIME)= checksum(b.S_NAME,b.S_ID,b.S_ID_NUMBER,b.S_TIME)
select * from S_INFO order by s_id_number descdrop table S_INFO /*
S_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小何 2 2022 2007-11-12 13:01:35.000
小李 1 201 2007-11-12 13:01:40.000
小小 3 103 2007-11-12 13:01:45.000
小焦 2 102 2007-11-12 13:01:50.000
小星 1 101 2007-11-12 13:01:55.000
小张 0 100 2007-11-12 13:02:00.000*/
(
S_NAME VARCHAR(32),/* 姓名 */
S_ID CHAR(8),/* 学生证号 */
S_ID_NUMBER numeric not null, /* 学生数字ID */
S_TIME DATEtime
)
--插入数据:
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小张', '0 ', 100, '11/12/2007 01:01:33 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小星', '1 ', 101, '11/12/2007 01:01:36 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小焦', '2 ', 102, '11/12/2007 01:01:39 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小李', '1 ', 201, '11/12/2007 01:01:45 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小何', '2 ', 2022, '11/12/2007 01:01:48 PM');
INSERT INTO S_INFO ( S_NAME, S_ID, S_ID_NUMBER, S_TIME ) VALUES (
'小小', '3 ', 103, '11/12/2007 01:01:42 PM'); goupdate a
set S_TIME = dateadd(s,5*b.px,'20071112 13:01:30')
from S_INFO a left join
(select * ,px = isnull((select count(1) from s_info where s_time >= c.s_time),0) from S_INFO c ) b
on checksum(a.S_NAME,a.S_ID,a.S_ID_NUMBER,a.S_TIME)= checksum(b.S_NAME,b.S_ID,b.S_ID_NUMBER,b.S_TIME)
select * from S_INFO order by s_id_number descdrop table S_INFO /*
S_NAME S_ID S_ID_NUMBER S_TIME
-------------------------------- -------- -------------------- ------------------------------------------------------
小何 2 2022 2007-11-12 13:01:35.000
小李 1 201 2007-11-12 13:01:40.000
小小 3 103 2007-11-12 13:01:45.000
小焦 2 102 2007-11-12 13:01:50.000
小星 1 101 2007-11-12 13:01:55.000
小张 0 100 2007-11-12 13:02:00.000*/
--35楼代码,简单,适用性广
update @s_info set s_time = dateadd(second,5*b.c,'11/12/2007 01:01:30 PM' )
from @s_info a,
( select s_id_number,(select count(*)+1 from @s_info where s_id_number > c.s_id_number) as c from @s_info c) b
where a.s_id_number = b.s_id_number
--36楼代码,复杂,受制于S_TIME,当把原表s_time清空就得不到正确结果。
update a
set S_TIME = dateadd(s,5*b.px,'20071112 13:01:30')
from S_INFO a left join
(select top 100 percent * ,px = isnull((select count(1) from s_info where s_time >= c.s_time),0) from S_INFO c order by s_id_number desc ) b
on checksum(a.S_NAME,a.S_ID,a.S_ID_NUMBER,a.S_TIME)= checksum(b.S_NAME,b.S_ID,b.S_ID_NUMBER,b.S_TIME)
set S_TIME = dateadd(s,5*b.px,'20071112 13:01:30')
from S_INFO a left join
(select * ,px = isnull((select count(1) from s_info where S_ID_NUMBER>= c.S_ID_NUMBER),0) from S_INFO c) b
on a.S_ID_NUMBER = b.S_ID_NUMBER
准备接分
我给个帖子你
帖子
DECLARE @D DATETIME
SET @D = 0UPDATE S_INFO
SET
@D = CASE WHEN @D = 0 THEN S_TIME ELSE DATEADD(SS,3,@D) END
,S_TIME = @D
SELECT * FROM S_INFO
SET S_TIME = DATEADD(ss, 5 *
(SELECT COUNT(S_ID_NUMBER)
FROM S_INFO AS S_INFO_1
WHERE S_ID_NUMBER < S_INFO.S_ID_NUMBER), '2007-11-12 13:01:30')
SET S_TIME = DATEADD(ss, 5 *
(SELECT COUNT(S_ID_NUMBER)
FROM S_INFO AS S_INFO_1
WHERE S_ID_NUMBER > S_INFO.S_ID_NUMBER), '2007-11-12 13:01:30')