比较有挑战性的SQL语句,我用游标完成,但是效果不理想,看看哪位高手能用一个SQL语句或者更简单的方法来完成
比较有挑战性的SQL语句,我用游标完成,但是效果不理想,看看哪位高手能用一个SQL语句或者更简单的方法来完成
表a:
id zimu type zorder
1 a 1 0
2 c 1 0
3 d 1 0
4 a 2 0
5 b 2 0
6 u 3 0
7 z 3 0
8 j 4 0
9 k 4 0
10 m 4 0
11 t 5 0
12 u 5 0
要求更新表a的zorder字段,使之变成如下的效果
id zimu type zorder
1 a 1 1
2 c 1 2
3 d 1 3
4 a 2 1
5 b 2 2
6 u 3 1
7 z 3 2
8 j 4 1
9 k 4 2
10 m 4 3
11 t 5 1
12 u 5 2
就是每个分类给他们排一下序
我的游标写法,效果非常差
declare @no as int
declare @id as int
declare @id1 as int
DECLARE cursor2 CURSOR FOR
SELECT [type]
FROM [a]
ORDER BY id desc
OPEN cursor2
FETCH NEXT FROM cursor2
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
set @id1=0
DECLARE cursor1 CURSOR FOR
SELECT [id]
FROM [a]
where [type]=@id
ORDER BY [id] desc
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @no
WHILE @@FETCH_STATUS = 0
BEGIN
set @id1=@id1+1
update [a] set zorder=@id1 where [id]=@no
FETCH NEXT FROM cursor1
INTO @no
end
CLOSE cursor1
DEALLOCATE cursor1
FETCH NEXT FROM cursor2
INTO @id
END
CLOSE cursor2
DEALLOCATE cursor2
[解决办法]
update t
set
zorder=(select count(*) from 表a where type=t.type and id <=t.id)
from
表a t
[解决办法]
更新
Update B Set zorder = (Select Count(id) From A Where type = B.type And id <= B.id) From A B
[解决办法]
drop table 表a
go
create table 表a(id int,zimu char(1),type int,zorder int)
insert into 表a
select 1, 'a ',1,0
union all select 2, 'c ',1,0
union all select 3, 'd ',1,0
union all select 4, 'a ',2,0
union all select 5, 'b ',2,0
union all select 6, 'u ',3,0
union all select 7, 'z ',3,0
union all select 8, 'j ',4,0
union all select 9, 'k ',4,0
union all select 10, 'm ',4,0
union all select 11, 't ',5,0
union all select 12, 'u ',5,0
update 表a
set zorder=(select count(*) from 表a b where a.type=b.type and a.zimu> =b.zimu)
from 表a a
select * from 表a
/*
id zimu type zorder
----------- ---- ----------- -----------
1 a 1 1
2 c 1 2
3 d 1 3
4 a 2 1
5 b 2 2
6 u 3 1
7 z 3 2
8 j 4 1
9 k 4 2
10 m 4 3
11 t 5 1
12 u 5 2
（所影响的行数为 12 行）
*/
