--求一update语句
--建测试数据
create table a(code varchar(10), dt datetime,seq int)
insert a(code,dt)
SELECT code, dt
FROM
(
select 'a001' AS code,'2004-11-12' AS dt
union
select 'a001','2004-11-20'
union
select 'a001','2004-11-25'
union
select 'b001','2004-11-05'
union
select 'b001','2004-11-03'
union
select 'b001','2004-11-25'
) AS a
ORDER BY code,dt
--求一更新seq字段的sql,要求先按code,dt排序后,
--按code分组,seq的值为各条记录在对应组中的顺序号,如是该组最后一条记录,则seq=100
--实现后的效果如下
select * from a order by code,dt
/*
code dt seq
a001 2004-11-12 00:00:00.000 1
a001 2004-11-20 00:00:00.000 2
a001 2004-11-25 00:00:00.000 100
b001 2004-11-03 00:00:00.000 1
b001 2004-11-05 00:00:00.000 2
b001 2004-11-25 00:00:00.000 100
*/DECLARE @code AS varchar(10),@i AS intUPDATE a SET @i = CASE WHEN @code =code THEN @i+1 ELSE 1 END, seq = @i, @code = code--select * from a order by code,dtUPDATE a
SET a.seq = 100
FROM a,(SELECT code,MAX(seq) AS seq FROM a GROUP BY code) AS b
WHERE a.code = b.code AND a.seq = b.seq
select * from a order by code,dtDROP TABLE a
--建测试数据
create table a(code varchar(10), dt datetime,seq int)
insert a(code,dt)
SELECT code, dt
FROM
(
select 'a001' AS code,'2004-11-12' AS dt
union
select 'a001','2004-11-20'
union
select 'a001','2004-11-25'
union
select 'b001','2004-11-05'
union
select 'b001','2004-11-03'
union
select 'b001','2004-11-25'
) AS a
ORDER BY code,dt
--求一更新seq字段的sql,要求先按code,dt排序后,
--按code分组,seq的值为各条记录在对应组中的顺序号,如是该组最后一条记录,则seq=100
--实现后的效果如下
select * from a order by code,dt
/*
code dt seq
a001 2004-11-12 00:00:00.000 1
a001 2004-11-20 00:00:00.000 2
a001 2004-11-25 00:00:00.000 100
b001 2004-11-03 00:00:00.000 1
b001 2004-11-05 00:00:00.000 2
b001 2004-11-25 00:00:00.000 100
*/DECLARE @code AS varchar(10),@i AS intUPDATE a SET @i = CASE WHEN @code =code THEN @i+1 ELSE 1 END, seq = @i, @code = code--select * from a order by code,dtUPDATE a
SET a.seq = 100
FROM a,(SELECT code,MAX(seq) AS seq FROM a GROUP BY code) AS b
WHERE a.code = b.code AND a.seq = b.seq
select * from a order by code,dtDROP TABLE a
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货