表数据任务号(varchar) 子序号(int) 名称(varchar)
001 null a
001 null b
001 null c
002 null d
002 null s
003 null d
004 null w
004 null s
004 null l
004 null k
...以上表中,【子序号】全为NULL,现求一语句,要求更新【子序号】为根据【任务号】分组的自增序号。即一下结果:任务号(varchar) 子序号(int) 名称(varchar)
001 1 a
001 2 b
001 3 c
002 1 d
002 2 s
003 1 d
004 1 w
004 2 s
004 3 l
004 4 k
...
如何不用游标来实现
001 null a
001 null b
001 null c
002 null d
002 null s
003 null d
004 null w
004 null s
004 null l
004 null k
...以上表中,【子序号】全为NULL,现求一语句,要求更新【子序号】为根据【任务号】分组的自增序号。即一下结果:任务号(varchar) 子序号(int) 名称(varchar)
001 1 a
001 2 b
001 3 c
002 1 d
002 2 s
003 1 d
004 1 w
004 2 s
004 3 l
004 4 k
...
如何不用游标来实现
goinsert t(id,name) values('001','a')
insert t(id,name) values('001','b')
insert t(id,name) values('002','c')
insert t(id,name) values('002','d')
insert t(id,name) values('002','e')
insert t(id,name) values('003','f')goupdate t set mid=isnull((select count(1)+1 from t where t.id=a.id and t.name>a.name),1) from t aselect * from t order by id,mid
========================================
http://www.dbtuning.cn
主营:中小企业数据库管理、优化、调校服务
========================================
goinsert t(id,name) values('001','a')
insert t(id,name) values('001','b')
insert t(id,name) values('002','c')
insert t(id,name) values('002','d')
insert t(id,name) values('002','e')
insert t(id,name) values('003','f')goupdate t set mid=isnull((select count(1)+1 from t where t.id=a.id and t.name>a.name),1) from t aselect * from t order by id,mid
-------------------------
001 1 b
001 2 a
002 1 e
002 2 d
002 3 c
003 1 f========================================
http://www.dbtuning.cn
主营:中小企业数据库管理、优化、调校服务
========================================
task nvarchar(5),
seq int,
task_name nvarchar(10)
)
insert into test select * from (
select '001' task ,null seq, 'a' task_name
union all select '001' ,null, 'b'
union all select '001' ,null, ' c'
union all select '002' ,null, ' d'
union all select '002' ,null, 's'
union all select '003' ,null, 'd'
union all select '004' ,null, 'w'
union all select '004' ,null, 's'
union all select '004' ,null, ' l'
union all select '004' ,null, ' k '
)aPRINT '批量更新开始!!!'
BEGIN
WITH UPD_CTE_LAST_YTD AS
( select a.seq as set_seq,b.seq as get_seq from test a
inner join(
select a.task,a.row_num-isnull(b.cnt,0) seq,a.task_name from (
select *,ROW_NUMBER ( ) OVER(order by task) row_num from test
)a
left join(
select a.task,sum(b.cnt) cnt from(
select task,count(*) cnt from test
group by task
)a
inner join(
select task,count(*) cnt from test
group by task
)b on b.task<a.task
group by a.task ) b on a.task=b.task
)b on a.task=b.task and a.task_name=b.task_name
)
UPDATE UPD_CTE_LAST_YTD
SET set_seq=get_seq
END
PRINT ' OK !!!'