求一很晕的sql语句

Question

表结构：id  key     parentkey    orderno 
1    1_                    1
2    1-1_     1_           1
3    1-2_     1_           2
4    1-3_     1_           3
5    1-4_     1_           4
6    1-1-1_   1-1_         1
7    1-1-2_   1-1_         2
8    1-3-1_   1-3_         1
9    1-3-2_   1-3_         2
10   1-4-1_   1-4_         1
11   1-3-1-1_ 1-3-1_       1 我在删除1-2_节点的时候 1-2_节点以后的所有节点都会往上走一步，比如 id为4的1-3_变为1-2_ id为4的 1-4_变成1-3_id为8和9 的节点是 id为4 的节点的子节点 他们的key要分别变成1-2-1_  1-2-2_  parentkey 要变为 1-2_不知道大家看明白了没。。不知道该怎么写。。晕死我了。大家帮帮忙啊。

Answer 1

SQL难呀，用pl/sql的过程来控制吧

Answer 2

需求倒是蛮普遍的，单纯的递归来实现恐怕也没那么简单，应该比较难，最好创建一个过程来做。给你个思路吧：
1、创建一个delete 触发器，
2、创建一个procedure，实现功能：更新你所有下级节点。
3、触发器中调用此过程，传的参数就要你:old.key,这样过程中才好根据你删除的该节点来，循环更新子节点。只能帮你有情顶一下了。

Answer 3

先查询出来删除节点后面所有的数据到程序里的一个集合中，再循环这个集合将数据都-1，然后更新到数据库中.
如果不允许用程序只能用plsql了

Answer 4

CREATE OR REPLACE PROCEDURE prc_delete_id( i in integer) is
  -- Local variables here  l_key varchar2(200) ;
  l_parentkey varchar2(200) ;
  l_orderno  varchar2(200) ;
  
  cursor cur is 
    select b.id,b.key,b.parentkey,b.orderno,substr(a.parentkey,1,length(a.parentkey)-1)||'-'||(a.orderno-1) newone
    from gyp_test1 a
         join gyp_test1 b on b.key like a.key||'%'
    where a.parentkey = (select parentkey from gyp_test1 where  id = i )
    and a.orderno > (select orderno from gyp_test1 where  id = i )
    ;
  
begin
  -- Test statements here  for cur1 in cur loop
      if instr(cur1.key,'-',length(cur1.newone)) = 0 then 
          l_key := cur1.newone||'_' ;
      else
          l_key := cur1.newone||substr(cur1.key,instr(cur1.key,'-',length(cur1.newone)));
      end if ;
    
      l_parentkey := substr(l_key,1,instr(l_key,'-',-1)-1)||'_';
      l_orderno := substr(l_key,instr(l_key,'-',-1)+1);
      l_orderno := substr(l_orderno,1,length(l_orderno)-1) ;
      
    --  dbms_output.put_line(cur1.id ||' '||l_key||' '||l_parentkey||' '||l_orderno) ;
      update gyp_test1 
             set key = l_key , parentkey = l_parentkey, orderno = l_orderno 
             where id = cur1.id ;
      commit;                           
  end loop    ;
  delete from gyp_test1 where id = i ;
  commit;
end;
/

Answer 5

代码如下（测试的是删除id=3记录），后面附上测试环境脚本DECLARE
   v_parentkey   test2.parentkey%TYPE;
   v_orderno     test2.orderno%TYPE;
BEGIN
   DELETE FROM test2
         WHERE ID = '3'  -- 修改为实际删除的id
     RETURNING parentkey, orderno
          INTO v_parentkey, v_orderno;   MERGE INTO test2 d
      USING (SELECT     ID, KEY, parentkey, orderno,
                           LTRIM
                              (   RTRIM (v_parentkey, '_')
                               || SYS_CONNECT_BY_PATH
                                             (CASE
                                                 WHEN parentkey = v_parentkey
                                                    THEN orderno - 1
                                                 ELSE TO_NUMBER (orderno)
                                              END,
                                              '-'
                                             ),
                               '-'
                              )
                        || '_' AS newkey,
                        CASE
                           WHEN parentkey = v_parentkey
                              THEN orderno - 1
                           ELSE TO_NUMBER (orderno)
                        END AS neworderno
                   FROM test2
             START WITH (parentkey = v_parentkey AND orderno > v_orderno)
             CONNECT BY parentkey = PRIOR KEY) s
      ON (d.ID = s.ID)
      WHEN MATCHED THEN
         UPDATE
            SET d.KEY = s.newkey, d.orderno = s.neworderno;
            
   COMMIT;
END;测试环境脚本
DROP TABLE TEST.TEST2 CASCADE CONSTRAINTS;CREATE TABLE TEST.TEST2
(
  ID         VARCHAR2(10 BYTE),
  KEY        VARCHAR2(10 BYTE),
  PARENTKEY  VARCHAR2(10 BYTE),
  ORDERNO    VARCHAR2(10 BYTE)
)
TABLESPACE USERS
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;SET DEFINE OFF;
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('1', '1_', NULL, '1');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('2', '1-1_', '1_', '1');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('4', '1-2_', '1_', '2');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('5', '1-3_', '1_', '3');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('6', '1-1-1_', '1-1_', '1');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('7', '1-1-2_', '1-1_', '2');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('8', '1-2-1_', '1-3_', '1');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('9', '1-2-2_', '1-3_', '2');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('10', '1-3-1_', '1-4_', '1');
Insert into TEST.TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('11', '1-2-1-1_', '1-3-1_', '1');
COMMIT;

Answer 6

前面那个把parentkey漏更新了，环境脚本也错了，写成更新后的数据情况了另外分析了下，实际可以用一句sql完成删除加更新，不过实际效率不如存储过程快了，因为里面涉及了多次嵌套查询主要思路：
真正会被影响的数据实际是满足下面条件的数据
1. 与被删除节点同层，且序号(orderno)在其之后的节点
2. 条件1中节点的子节点
这个想清楚了，利用oracle的Hierarchical Query重新生成节点间的关系，然后对应更新即可了不过，这种删除一个节点就要更新大量相关节点的设计可能不是很合适，如果影响的节点多，或者变动频率较高时，这个更新的开销就大了，建议不要去变动描述节点关系的key和parentkey的值，这个应该是作为内部技术数据使用，其实删除一个叶节点，其他节点不更新，树结构也是正确的SQL版
MERGE INTO test2 d
   USING (SELECT ID, KEY, parentkey, orderno, newkey, neworderno,
                    SUBSTR (newkey,
                            1,
                            INSTR (newkey, '-', -1) - 1
                           )
                 || '_' AS newparentkey
            FROM (SELECT     ID, KEY, parentkey, orderno,
                                LTRIM
                                   (   RTRIM ((SELECT parentkey
                                                 FROM test2
                                                WHERE ID = :v_id), '_')
                                    || SYS_CONNECT_BY_PATH
                                                  (DECODE (LEVEL,
                                                           1, orderno - 1,
                                                           TO_NUMBER (orderno)
                                                          ),
                                                   '-'
                                                  ),
                                    '-'
                                   )
                             || '_' AS newkey,
                             DECODE (LEVEL,
                                     1, orderno - 1,
                                     TO_NUMBER (orderno)
                                    ) AS neworderno,
                             LEVEL lv
                        FROM test2
                  START WITH parentkey = (SELECT parentkey
                                            FROM test2
                                           WHERE ID = :v_id)
                         AND orderno >= (SELECT orderno
                                           FROM test2
                                          WHERE ID = :v_id)
                  CONNECT BY parentkey = PRIOR KEY)) s
   ON (d.ID = s.ID)
   WHEN MATCHED THEN
      UPDATE
         SET d.KEY = s.newkey, d.orderno = s.neworderno,
             d.parentkey = s.newparentkey
      DELETE
         WHERE ID = :v_id;DECLARE
   v_parentkey   test2.parentkey%TYPE;
   v_orderno     test2.orderno%TYPE;
BEGIN
   DELETE FROM test2
         WHERE ID = '3'                                 -- 修改为实际删除的id
     RETURNING parentkey, orderno
          INTO v_parentkey, v_orderno;   MERGE INTO test2 d
      USING (SELECT ID, KEY, parentkey, orderno, newkey, neworderno,
                       SUBSTR (newkey,
                               1,
                               INSTR (newkey, '-', -1) - 1
                              )
                    || '_' AS newparentkey
               FROM (SELECT     ID, KEY, parentkey, orderno,
                                   LTRIM
                                      (   RTRIM (v_parentkey, '_')
                                       || SYS_CONNECT_BY_PATH
                                                  (DECODE (LEVEL,
                                                           1, orderno - 1,
                                                           TO_NUMBER (orderno)
                                                          ),
                                                   '-'
                                                  ),
                                       '-'
                                      )
                                || '_' AS newkey,
                                DECODE (LEVEL,
                                        1, orderno - 1,
                                        TO_NUMBER (orderno)
                                       ) AS neworderno
                           FROM test2
                     START WITH (    parentkey = v_parentkey
                                 AND orderno > v_orderno
                                )
                     CONNECT BY parentkey = PRIOR KEY)) s
      ON (d.ID = s.ID)
      WHEN MATCHED THEN
         UPDATE
            SET d.KEY = s.newkey, d.orderno = s.neworderno,
                d.parentkey = s.newparentkey
         ;
   COMMIT;
END;
DROP TABLE TEST2 CASCADE CONSTRAINTS;CREATE TABLE TEST2
(
  ID         VARCHAR2(10 BYTE),
  KEY        VARCHAR2(10 BYTE),
  PARENTKEY  VARCHAR2(10 BYTE),
  ORDERNO    VARCHAR2(10 BYTE)
)
TABLESPACE USERS;
CREATE INDEX IND1_TEST2 ON TEST2
(ID)
TABLESPACE USERS;SET DEFINE OFF;
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('1', '1_', NULL, '1');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('2', '1-1_', '1_', '1');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('3', '1-2_', '1_', '2');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('4', '1-3_', '1_', '3');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('5', '1-4_', '1_', '4');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('6', '1-1-1_', '1-1_', '1');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('7', '1-1-2_', '1-1_', '2');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('8', '1-3-1_', '1-3_', '1');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('9', '1-3-2_', '1-3_', '2');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('10', '1-4-1_', '1-4_', '1');
Insert into TEST2
   (ID, KEY, PARENTKEY, ORDERNO)
 Values
   ('11', '1-3-1-1_', '1-3-1_', '1');
COMMIT;

Answer 7

碰到个很怪的问题,绕不过去.
SQL>    select '1'||substr(t.key,2+1,instr(replace(t.key,'_','-'),'-',2+1,1)-2-1)-1,t.*
  2     from tbl_delete t
  3     where t.key like '1-%' and t.key>'1-2_'
  4  ;
 
'1'||SUBSTR(T.KEY,2+1,INSTR(RE          ID KEY                                                                              PARENTKEY                                                                            ORDERNO
------------------------------ ----------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
                            12           4 1-3_                                                                             1_                                                                                         3
                            13           5 1-4_                                                                             1_                                                                                         4
                            12           8 1-3-1_                                                                           1-3_                                                                                       1
                            12           9 1-3-2_                                                                           1-3_                                                                                       2
                            13          10 1-4-1_                                                                           1-4_                                                                                       1
                            12          11 1-3-1-1_                                                                         1-3-1_                                                                                     1
 
6 rows selected
 
SQL> 
SQL>    select '1-'||substr(t.key,2+1,instr(replace(t.key,'_','-'),'-',2+1,1)-2-1)-1,t.*
  2     from tbl_delete t
  3     where t.key like '1-%' and t.key>'1-2_'
  4  ;
 
select '1-'||substr(t.key,2+1,instr(replace(t.key,'_','-'),'-',2+1,1)-2-1)-1,t.*
   from tbl_delete t
   where t.key like '1-%' and t.key>'1-2_'
 
ORA-01722: invalid number
 
SQL>