我有表格及数据如下:
编号 时间 数量
0100001 2009-9-28 13:18 18
0100002 2009-9-28 13:18 29
0100002 2009-9-28 13:45 36
0100003 2009-9-28 13:45 42
0100004 2009-9-28 13:45 11
0100001 2009-9-28 13:45 92
0100003 2009-9-29 10:45 26
0100004 2009-9-29 10:45 100我用下面的语句找出编号为0100003的最后一条记录(以时间判断)
select top 1 * from tbl_studentuserecord where stu_no like '0100003' order by stu_statime DESC请教:用什么语句可以修改这找出来的这条记录里的'数量'?就是说怎么修改某一编号的最后一条记录的'数量'?
编号 时间 数量
0100001 2009-9-28 13:18 18
0100002 2009-9-28 13:18 29
0100002 2009-9-28 13:45 36
0100003 2009-9-28 13:45 42
0100004 2009-9-28 13:45 11
0100001 2009-9-28 13:45 92
0100003 2009-9-29 10:45 26
0100004 2009-9-29 10:45 100我用下面的语句找出编号为0100003的最后一条记录(以时间判断)
select top 1 * from tbl_studentuserecord where stu_no like '0100003' order by stu_statime DESC请教:用什么语句可以修改这找出来的这条记录里的'数量'?就是说怎么修改某一编号的最后一条记录的'数量'?
set ..
from ta a
where exists(select top 1.......)
set ...
where stu_no like '0100003' and stu_statime
= (select top 1 stu_statime from tbl_studentuserecord where stu_no like '0100003' order by stu_statime DESC )
select * from tb t
where not exists(select * from tb where 编号=t.编号 and 时间 >t.时间)
and 编号='0100003'---
update
t
set
编号=你要修改的值
from
tb t
where
exists(select top 1 * from tbl_studentuserecord where stu_no like '0100003' order by stu_statime DESC )
update tbl_studentuserecord set 数量='1' where
stu_no =(select top 1 stu_no from tbl_studentuserecord where stu_no like '0100003' order by stu_statime DESC )
from tbl_studentuserecord a
(
select 编号,max(日期) as 日期 from tbl_studentuserecord group by 编号
)a
where a.编号=b.编号 and a.日期=日期试先
if object_ID('tb') IS NOT NULL DROP TABLE tb
go
create table tb(编号 varchar(10), 时间 datetime ,数量 int)
go
insert tb select
'0100001', '2009-9-28 13:18', 18 union all select
'0100002', '2009-9-28 13:18', 29 union all select
'0100002', '2009-9-28 13:45', 36 union all select
'0100003', '2009-9-28 13:45', 42 union all select
'0100004', '2009-9-28 13:45', 11 union all select
'0100001', '2009-9-28 13:45', 92 union all select
'0100003', '2009-9-29 10:45', 26 union all select
'0100004', '2009-9-29 10:45', 100 select * from tb t
where not exists(select * from tb where 编号=t.编号 and 时间 >t.时间)
and 编号='0100003'
/*
编号 时间 数量
---------- ----------------------- -----------
0100003 2009-09-29 10:45:00.000 26
*/
update tb
set 数量=100000
from tb t
where not exists(select * from tb where 编号=t.编号 and 时间 >t.时间)
and 编号='0100003'
select * from tb编号 时间 数量
---------- ----------------------- -----------
0100001 2009-09-28 13:18:00.000 18
0100002 2009-09-28 13:18:00.000 29
0100002 2009-09-28 13:45:00.000 36
0100003 2009-09-28 13:45:00.000 42
0100004 2009-09-28 13:45:00.000 11
0100001 2009-09-28 13:45:00.000 92
0100003 2009-09-29 10:45:00.000 100000
0100004 2009-09-29 10:45:00.000 100(8 行受影响)
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(7),[时间] datetime,[数量] int)
insert [tb]
select '0100001','2009-9-28 13:18',18 union all
select '0100002','2009-9-28 13:18',29 union all
select '0100002','2009-9-28 13:45',36 union all
select '0100003','2009-9-28 13:45',42 union all
select '0100004','2009-9-28 13:45',11 union all
select '0100001','2009-9-28 13:45',92 union all
select '0100003','2009-9-29 10:45',26 union all
select '0100004','2009-9-29 10:45',100
--> 测试数据:[tb1] --这个是你要更新的每个编号的值
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([编号] varchar(7),[数量] int)
insert [tb1]
select '0100001',12 union all
select '0100002',15 union all
select '0100003',18 union all
select '0100004',20select * from [tb]
update m1 set 数量=新数量 from
(
select m.编号,m.数量, tb1.数量 as 新数量 from
(
select * from tb t where not exists(select 1 from tb where 编号=t.编号 and 时间>t.时间)
) m,tb1
where m.编号=tb1.编号
) m1
select * from [tb]/*
编号 时间 数量
------- ----------------------- -----------
0100001 2009-09-28 13:18:00.000 18
0100002 2009-09-28 13:18:00.000 29
0100002 2009-09-28 13:45:00.000 15
0100003 2009-09-28 13:45:00.000 42
0100004 2009-09-28 13:45:00.000 11
0100001 2009-09-28 13:45:00.000 12
0100003 2009-09-29 10:45:00.000 18
0100004 2009-09-29 10:45:00.000 20(8 行受影响)
*/