34,575
社区成员
发帖
与我相关
我的任务
分享
1 2 1
2 3 3
2 3 5
2 3 2
更新第一个3时候,由于select count(1)+1 from test where ClassType=@ClassType and OrderNum<a.OrderNum(select count(1)+1 from test where ClassType=3 and OrderNum<3)=2
则更新3=>2,
更新5时候,(select count(1)+1 from test where ClassType=3 and OrderNum<5)=1
则更新3=>1
以下类似。。
if object_id('test') is not null drop table test
create table test([ID] int,[ClassType] int,[OrderNum] int)
insert test
select 1,2,1 union all
select 2,3,3 union all
select 2,3,5 union all
select 2,3,2
select * from test
--------------------
1 2 1
2 3 3
2 3 5
2 3 2
create PROCEDURE Inc_OrderNum
(
@ClassType int
)
AS
begin
update test
set OrderNum=
(select count(1)+1 from test where ClassType=@ClassType and OrderNum<a.OrderNum) from test a
where
ClassType=@ClassType
end
-------------------
exec Inc_OrderNum 3
--------------------
1 2 1
2 3 2
2 3 3
2 3 1
--这样的话只能用
create proc Inc_OrderNum @ClassType int
as
begin
update t
set OrderNum = (select count(1) from MyTab where OrderNum<=t.OrderNum and ClassType=t.ClassType)
from MyTab t
where ClassType=@ClassType
end
--或者用临时表
if object_id('test') is not null drop table test
create table test([ID] int,[ClassType] int,[OrderNum] int)
insert test
select 1,2,1 union all
select 2,3,3 union all
select 2,3,4
select * from test
create PROCEDURE Inc_OrderNum
(
@ClassType int
)
AS
begin
update test
set OrderNum=
(select count(1)+1 from test where ClassType=@ClassType and OrderNum<a.OrderNum) from test a
where
ClassType=@ClassType
end
exec Inc_OrderNum 3
---------------------
1 2 1
2 3 1
2 3 2
if object_id('[MyTab]') is not null drop table [MyTab]
create table [MyTab]([ID] int,[ClassType] int,[OrderNum] int)
insert [MyTab]
select 1,2,1 union all
select 2,3,3 union all
select 2,3,5
--存储过程
create proc Inc_OrderNum @ClassType int
as
begin
declare @i int
select @i = 0
update MyTab
set OrderNum = @i,
@i=@i+1
where ClassType=@ClassType
end
--执行
exec Inc_OrderNum 3
--结果
select * from [MyTab]
--------------------
1 2 1
2 3 1
2 3 2
create proc Inc_OrderNum @ClassType int
as
begin
declare @i int
select @i = 0
update MyTab
set OrderNum = @i,
@i=@i+1
where ClassType=@ClassType
end
update A
set OrderNum=(select count(1)+1 from MyTab where ClassType=@ClassType
and OrderNum < A.OrderNum)
from MyTab A
where ClassType=@ClassType