求一个递归查询SQL

newkid

kylemo 发表于 2017-5-4 12:09
1、任何相邻两级的名字不能相同 是的
2、如果儿子和孙子同名，那么就把孙子改成爷爷名字？对
3、如果曾 ...

根据你这个最新回答，这并没有递归，因为结果都是可静态确定的，两次自连接就可以了：


WITH X AS
(SELECT '1' AS id, '1' AS pid, 'TEST' AS treeNm, '李明' AS userName FROM dual
  UNION
  SELECT '2' AS id, '1' AS pid, 'TEST\AA' AS treeNm, '李华' AS userName  FROM dual
  UNION
  SELECT '3' AS id, '2' AS pid, 'TEST\AA\BB' AS treeNm, '何思可' AS userName  FROM dual
  UNION
  SELECT '4' AS id, '3' AS pid, 'TEST\AA\BB\CC' AS treeNm, '何思可' AS userName  FROM dual
  UNION
  SELECT '5' AS id, '3' AS pid, 'TEST\AA\BB\DD' AS treeNm, '吴小明' AS userName FROM dual
  UNION
  SELECT '6' AS id, '4' AS pid, 'TEST\AA\BB\DD\EE' AS treeNm,  '吴小明' AS userName FROM dual
  UNION
  SELECT '7' AS id, '4' AS pid, 'TEST\AA\BB\DD\JJ11' AS treeNm, '吴小明' AS userName FROM dual
  UNION
  SELECT '8' AS id,'4' AS pid, 'TEST\AA\BB\DD\EE22' AS treeNm, '吴小明' AS userName FROM dual)
SELECT son.*
      ,CASE WHEN son.username=father.username and son.id<>father.id then grand_father.username
            ELSE son.username
       END as new_username
  FROM x grand_father,x father,x son
WHERE grand_father.id=father.pid and father.id=son.pid ;

你的例子不能自圆其说，6,7,8的treenm和父亲id并不符合，令人迷惑。