求一个自定义排序的存储过程

Question

TO:gc_ding(E.T) 因为3号跟5号中间断号了。所以此时5号，8号不能加1。

Answer 1

两种思路：
１、变量循环　定义一个变量，作为循环计数器，从１开始循环，每次都将第一个在表格中不存在的值填充到表格且跳出循环；如果是一次填充多个，就在多个都填充完后才跳出循环；
２、如果填充的是一个物理临时表，为提高效率，可以建一个只有序列字段的物理表并为它填充尽量多的值后建立升序索引，每次需要定义记录序列号时，直接执行查询：select min(临时id)　from 临时表　where not exists (select 1 from 目标表　where 填充序列字段　= 临时表.临时id)　来获取最新的可以填充的序列号

Answer 2

看看这个行么create procedure pro(@num int)
as
declare @temp_id int
set @temp_id=''
declare @cur_num int
declare cur_num cursor for
select id from 表名 order by id
open cur_numfetch next from cur_num into @cur_num
while @@fetch_status = 0 
begin
 if @cur_num=@num 
 begin
  set @temp_id=@cur_num+1
  update 表名 set id=@cur_num+1 where id=@cur_num
 end
 else if @cur_num=@temp_id
 begin
  update 表名 set id=@temp_id+1 where id=@cur_num
  set @temp_id=@cur_num+1
 end
 fetch next from cur_num into @cur_numend
close cur_num
deallocate cur_numgo

Answer 3

CREATE TRIGGER tg_insertdata ON [dbo].[num] 
instead of  INSERT
AS
  begin
      if exists(select id from num where id = (select id from inserted))
         begin
             -------------------如果存在此记录的话
            declare @middle varchar(20)
            select @middle=id  from inserted  
            declare @table table  (cid int)
            insert into @table select id from num where id=@middle
            set @middle=@middle+1
            while @@rowcount > 0
                begin
                    if exists(select id from num where id=@middle)
                      begin
                          insert into @table select @middle
                          set @middle=@middle+1
                      end
                   else
                     begin
                         break
                     end
                end
           update num set id=id+1 where id in (select cid from @table)
           insert into num select  id  from inserted
         end 
     else
        begin
            insert into num select  id  from inserted
        end
  end

Answer 4

create table ta(id int)
insert ta
select 1 
union all select 2
union all select 3
union all select 5
union all select 8创建存储过程:create proc test_p @id int
as
update a 
set id=id+1
from ta a 
where (exists(select 1 from ta where a.id=id+1) 
or exists(select 1 from ta where a.id=id-1) )
and id!<@id --更新insert ta select @id--插入测试:
exec test_p 2select * from ta order by id
id          
----------- 
1
2
3
4
5
8（所影响的行数为 6 行）

Answer 5

create table ta(id int)
insert ta
select 1 
union all select 2
union all select 3
union all select 5
union all select 8用触发器:
create trigger test_tr on ta
instead of insert
as
begin
DECLARE @i int
DECLARE roy CURSOR
FOR SELECT * from inserted
OPEN roy
FETCH next FROM roy into @i
WHILE @@FETCH_STATUS = 0
begin
if exists(select 1 from ta where id=@i)
begin
update a 
set id=id+1
from ta a 
where (exists(select 1 from ta where a.id=id+1) 
or exists(select 1 from ta where a.id=id-1) )
and id!<@i
end
insert ta select @i
FETCH NEXT FROM roy INTO @i
end
CLOSE roy
DEALLOCATE roy
end--测试
insert ta select 1 union all select 6查询:select * from ta order by id
id          
----------- 
1
2
3
4
5
6
8（所影响的行数为 7 行）

Answer 6

楼上帅哥提得好,由于楼主的列子也没有考虑到这种情况。偶发完贴完也考虑到这个问题，不能连发三贴。
以下贴出正确答案。
create table ta(id int)
insert ta
select 1
union all select 2
union all select 3
union all select 6
union all select 7
union all select 12
union all select 13用存储过程:
create proc test_p @id int
as
begin
declare @j int,@sql varchar(1000)
if exists(select 1 from ta where id=@id)
begin
select @j=@id,@sql=''
while exists(select 1 from ta where id=@j)
begin
select @sql=@sql+','+rtrim(id) from ta where id=@j
select @j=@j+1
end
set @sql=stuff(@sql,1,1,'')
exec('update ta set id=id+1 where id in ('+@sql+')')
insert ta select @id
end
else
insert ta select @id
end测试:
exec test_p 1
exec test_p 8
--truncate table ta 清空表数据用触发器:
create trigger test_tr on ta
instead of insert
as
begin
declare @id int,@j int,@sql varchar(1000)
declare roy cursor 
for select id from inserted
open roy
fetch next from roy into @id
while @@fetch_status=0
begin
if exists(select 1 from ta where id=@id)
begin
     select @j=@id,@sql=''
     while exists(select 1 from ta where id=@j)
           begin
                select @sql=@sql+','+rtrim(id) from ta where id=@j
                select @j=@j+1
           end
      set @sql=stuff(@sql,1,1,'')
      exec('update ta set id=id+1 where id in ('+@sql+')')
end
insert ta select @id
fetch next from roy into @id
end
CLOSE roy
DEALLOCATE roy
end测试:
insert ta
select 1
union all select 2
union all select 3
删除测试:
drop proc test_p
drop table ta

Answer 7

roy_88大哥，不行啊，我的表有好几个字段

Answer 8

to:bit_byte() ( ) 信誉：100    Blog 
declare @id int,@j int,@sql varchar(1000)--@id是定义的变量列,定义多个就行了.
你把列的属性都贴出来给你改一下就行了
insert ta select @id,.....增加新增的字段如果只插入id其它列默认null时定义方法
create proc test_p (@id int
as
begin
declare @j int,@sql varchar(1000)
if exists(select 1 from ta where id=@id)
begin
select @j=@id,@sql=''
while exists(select 1 from ta where id=@j)
begin
select @sql=@sql+','+rtrim(id) from ta where id=@j
select @j=@j+1
end
set @sql=stuff(@sql,1,1,'')
exec('update ta set id=id+1 where id in ('+@sql+')')
insert ta(id) select @id--在这里用()指定列
end
else
insert ta(id) select @id--在这里用()指定名
end

Answer 9

以上的create proc test_p (@id int--把'('去掉
如果是触发器
insert ta select @id
改为
insert ta(id) select  @id --除id列外,其它列为null

Answer 10

update ta  set id=ta.id+1  
where id>=5(要插入的id)
and id<=(select min(a.id+1) min_id FROM ta a
left join ta b on a.id=b.id-1
where a.id>=5(要插入的id) and b.id is null)insert into ta(id,...)
select 5(要插入的id)
,....select * from ta

Answer 11

create proc test_p (@id int
as
begin
update ta  set id=ta.id+1  
where id>=@id and id<=(select min(a.id+1) min_id FROM ta a
left join ta b on a.id=b.id-1
where a.id>=@id  and b.id is null)insert into ta(id,...)
select @id,...
end

Answer 12

create proc test_p (@id int)
as
begin
update ta  set id=ta.id+1  
where id>=@id and id<=(select min(a.id+1) min_id FROM ta a
left join ta b on a.id=b.id-1
where a.id>=@id  and b.id is null)insert into ta(id)
select @id
end刚才少了一个')'

Answer 13

xiequanqin(XQQ) 大哥，这样修改了一下，每次插入一条记录，所以大于@pid的数都加上了1，我希望如果有断号，后面就停止加1，下面是我修改后的代码，仍然没有达到我期望的要求。再请指教。roy_88大哥的代码不大看懂~~惭愧~~
alter proc test_p 
(
@pid int,
@standard char(20),
@name_zh nvarchar(20),
@name_en char(50),
@img_path char(50)
)
as
begin
update product  set pid=product.pid+1  
where pid>=@pid and pid<=(select min(a.pid+1) min_id FROM product a
left join product b on a.pid=b.pid-1
where a.pid>=@pid  and b.pid is null)insert into product(pid,standard,name_zh,name_en,img_path)
select @pid,@standard,@name_zh,@name_en,@img_path
end

Answer 14

xiequanqin(XQQ) 大哥，这样修改了一下，每次插入一条记录，所以大于@pid的数都加上了1，我希望如果有断号，后面就停止加1，下面是我修改后的代码，仍然没有达到我期望的要求。再请指教。roy_88大哥的代码不大看懂~~惭愧~~
alter proc test_p 
(
@pid int,
@standard char(20),
@name_zh nvarchar(20),
@name_en char(50),
@img_path char(50)
)
as
begin
update product  set pid=product.pid+1  
where pid>=@pid and pid<=(select min(a.pid+1) min_id FROM product a
left join product b on a.pid=b.pid-1
where a.pid>=@pid  and b.pid is null)insert into product(pid,standard,name_zh,name_en,img_path)
select @pid,@standard,@name_zh,@name_en,@img_path
end

Answer 15

xiequanqin(XQQ) 大哥，这样修改了一下，每次插入一条记录，所以大于@pid的数都加上了1，我希望如果有断号，后面就停止加1，下面是我修改后的代码，仍然没有达到我期望的要求。再请指教。roy_88大哥的代码不大看懂~~惭愧~~
alter proc test_p 
(
@pid int,
@standard char(20),
@name_zh nvarchar(20),
@name_en char(50),
@img_path char(50)
)
as
begin
update product  set pid=product.pid+1  
where pid>=@pid and pid<=(select min(a.pid+1) min_id FROM product a
left join product b on a.pid=b.pid-1
where a.pid>=@pid  and b.pid is null)insert into product(pid,standard,name_zh,name_en,img_path)
select @pid,@standard,@name_zh,@name_en,@img_path
end

Answer 16

create table product(
pid int,
standard char(20),
name_zh nvarchar(20),
name_en char(50),
img_path char(50)
)
GOalter proc test_p 
(
@pid int,
@standard char(20),
@name_zh nvarchar(20),
@name_en char(50),
@img_path char(50)
)
as
begin
update product  set pid=product.pid+1  
where pid>=@pid and pid<=ISNULL((select min(a.pid+1) min_id FROM product a
left join product b on a.pid=b.pid-1
where a.pid>=@pid  and b.pid is null 
AND EXISTS(SELECT pid from product where pid=@pid)
),-1)insert into product(pid,standard,name_zh,name_en,img_path)
select @pid,@standard,@name_zh,@name_en,@img_path
end
GOexec  test_p 15,'aaaa','aa1','bb1','test1'select * from product呵呵。。少考虑了在最前面插入的情况

Answer 17

问题已解决，马上结帖，各位朋友能留下MSN或QQ吗。

Answer 18

可以交个朋友
以后多多交流这个 用户名+Hotmail邮箱~