--插入数据后用存储过程实现
create proc p_insert
@seq_no varchar(20) --新记录编号
,@seq_no_b varchar(20) --要放在那个编号前面
as
update t1 set
child=(select child from t1 where seq_no=@seq_no_b)
where seq_no=@seq_noupdate t1 set
child=(select top 1 child
from t1
where parent=a.parent and child>a.child
order by child)
from t1 a
where seq_no>=@seq_no_b and
parent=(select parent from t1 where seq_no=@seq_no)
create proc p_insert
@seq_no varchar(20) --新记录编号
,@seq_no_b varchar(20) --要放在那个编号前面
as
update t1 set
child=(select child from t1 where seq_no=@seq_no_b)
where seq_no=@seq_noupdate t1 set
child=(select top 1 child
from t1
where parent=a.parent and child>a.child
order by child)
from t1 a
where seq_no>=@seq_no_b and
parent=(select parent from t1 where seq_no=@seq_no)
解决方案 »
- 应收款报表,最好能写个存储过程,谢谢
- select id from A where id in (select id from B) 怎样提高执行速度?
- 一个CASE WHEN中的查询问题
- 请问"多表查询"时返回的记录集也是独立的一行一行,而不是被串联在一行上?
- 大家好,我想通过JDBC访问一台安全性设置为Windows验证的SQL Server,请问如何写创建数据库连接的程序呀?
- 菜鸟问题:如何保持表内信息的同步?
- Informix和SQL Sever的区别?
- 如何打包sql 2000的dts包
- 用union连接多个表的修改错误
- 有没有办法更改数据库服务器的实例名
- 这个++连接到底哪里出错?
- 怎么把表中的这些记录清出掉(要求考虑效率,记录条数超大)
create table t1
(seq_no varchar(20),
child varchar(20),
parent varchar(20))
create table t1
insert into t1(seq_no,child,parent)
(select 'XXXX001', 'ZG0101001', 'ZG0101'
union all
select 'XXXX002', 'ZG0101002' ,'ZG0101'
union all
select'XXXX003', 'ZG0101004', 'ZG0101'
union all
select 'XXXX004' ,'ZG0101005' ,'ZG0101' union all
select 'XXXX005', 'ZG0101006', 'ZG0101'
union all
select'XXXX006', 'ZG0101007', 'ZG0101'
union all
select'XXXX007', 'ZG0102001', 'ZG0102'
union all
select 'XXXX008', 'ZG0102002', 'ZG0102'
union all
select 'XXXX009', 'ZG0102003', 'ZG0102'
)__________________________
create proc p_insert
@seq_no varchar(20) --新记录编号
,@seq_no_b varchar(20) --要放在那个编号前面
as
update t1 set
child=(select child from t1 where seq_no=@seq_no_b)
where seq_no=@seq_noupdate t1 set
child=(select top 1 child
from t1
where parent=a.parent and child>a.child
order by child)
from t1 a
where seq_no>=@seq_no_b and
parent=(select parent from t1 where seq_no=@seq_no)
____________________________
执行exec p_insert 'XXXX0010','ZG0101003'
所影响0行
@seq_no varchar(20) --新记录编号
,@seq_no_b varchar(20) --要放在那个编号前面
as
--取要插入编号下一编号的child值
declare @child varchar(20)
select top 1 @child=child
from t1
where parent=(select parent from t1 where seq_no=@seq_no_b)
and child>(select child from t1 where seq_no=@seq_no_b)
order by child--交换新编号和原有最大编号的child值
update t1 set child=b.child
from t1 a
,(select top 2 seq_no,child
from t1
where parent=(select parent from t1 where seq_no=@seq_no)
order by child desc
) b
where a.seq_no<>b.seq_no and
a.seq_no in
(select top 2 seq_no
from t1
where parent=(select parent from t1 where seq_no=@seq_no)
order by child desc)--交换新编号和要插入编号的child值
update t1 set child=b.child
from t1 a
,(select seq_no,child from t1 c where seq_no in (@seq_no,@seq_no_b)) b
where a.seq_no<>b.seq_no and a.seq_no in (@seq_no,@seq_no_b)--更新非新编号和原有最大编号的child值
update t1 set
child=(select top 1 child
from t1
where parent=a.parent and child>a.child
order by child)
from t1 a
where seq_no>=@seq_no_b and seq_no not in
(select top 2 seq_no
from t1
where parent=(select parent from t1 where seq_no=@seq_no)
order by seq_no desc)
and parent=(select parent from t1 where seq_no=@seq_no)--更新原有编号child值
update t1 set child=@child where seq_no=@seq_no_b
insert into t1 select 'XXXX001','ZG0101001','ZG0101'
union all select 'XXXX002','ZG0101002','ZG0101'
union all select 'XXXX003','ZG0101004','ZG0101'
union all select 'XXXX004','ZG0101005','ZG0101'
union all select 'XXXX005','ZG0101006','ZG0101'
union all select 'XXXX006','ZG0101007','ZG0101'
union all select 'XXXX007','ZG0102001','ZG0102'
union all select 'XXXX008','ZG0102002','ZG0102'
union all select 'XXXX009','ZG0102003','ZG0102'insert into t1 select 'XXXX010','ZG0101008','ZG0101'exec p_insert 'XXXX010','XXXX002'select * from t1 order by parent,child--返回seq_no child parent
-------------------- -------------------- --------------------
XXXX001 ZG0101001 ZG0101
XXXX010 ZG0101002 ZG0101
XXXX002 ZG0101004 ZG0101
XXXX003 ZG0101005 ZG0101
XXXX004 ZG0101006 ZG0101
XXXX005 ZG0101007 ZG0101
XXXX006 ZG0101008 ZG0101
XXXX007 ZG0102001 ZG0102
XXXX008 ZG0102002 ZG0102
XXXX009 ZG0102003 ZG0102(所影响的行数为 10 行)
似乎有点问题
insert into t1 select 'XXXX011','ZG0101003','ZG0101'
exec p_insert 'XXXX011','XXXX003'
这是插入新记录后所重新执行存储过程的结果seq_no child parent
-------------------- -------------------- --------------------
XXXX005 NULL ZG0101
XXXX001 ZG0101001 ZG0101
XXXX002 ZG0101002 ZG0101
XXXX011 ZG0101004 ZG0101
XXXX003 ZG0101005 ZG0101
XXXX004 ZG0101006 ZG0101
XXXX006 ZG0101006 ZG0101
XXXX007 ZG0102001 ZG0102
XXXX008 ZG0102002 ZG0102
XXXX009 ZG0102003 ZG0102
___________________________________________________update t1 set
child=(select top 1 child
from t1
where parent=a.parent and child>a.child
order by child)
from t1 awhere seq_no>=@seq_no_b and seq_no not in //有点问题
(select top 2 seq_no
from t1
where parent=(select parent from t1 where seq_no=@seq_no)
order by seq_no desc)
and parent=(select parent from t1 where seq_no=@seq_no)
_________________________________
我这里的seq_no是不能比较大小的,他与顺序无关,是用来唯一标实记录用的
from t1
where parent=(select parent from t1 where seq_no=@seq_no)
order by seq_no desc) --这里可以改为 order by child desc)
and parent=(select parent from t1 where seq_no=@seq_no)--要求新插入的child是最大的。
如果插入的child值在现有的之间,根本就不用调用该存储过程,因为你取出是按照child排序的:
select * from t1 order by parent,child
也可以在存储过程里加上判断,如果插入数据child值不是最大,不作修改直接返回。
……
as
if exists
(select 1 from t1 where child>(select child from t1 where seq_no=@seq_no))
return
……
--另外也不能插入同一parent下跟现有child重复的,可以取现有最大值+1插入。
重复的情况下,程序不会出错,但操作是没有意义的,可以直接返回