求一个递归查询SQL

kylemo · 发表于 2017-5-3 09:31

我写一个，抛砖引玉，只实现相邻两级的记录替换，夸级的还没实现

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),
Y AS
(SELECT t.*,
CASE
WHEN (SELECT USERNAME
FROM X a
WHERE a.pid = t.pid - 1
AND ROWNUM = 1) = t.USERNAME THEN
(SELECT USERNAME
FROM X a
WHERE a.pid = t.pid - 2
AND ROWNUM = 1)
ELSE
t.USERNAME
END AS USERNAME_C
FROM X t)
SELECT * FROM Y

复制代码

fage090 · 发表于 2017-5-3 10:45

“例如1：当取到当前记录是第3条时，username为何思可，其上一级（第2条记录）为李华，则不需要变更；当取当第4要记录时，username为何思可，其上一级（第3条记录）也是何思可，
则需要变更为（第2条记录）李华；例如2：当前记录为第8条时，其username应该变更为何思可（直到找不同的值为止）”

直接被绕晕了。。。为什么当前记录是第3条时，不用变更；当记录是第8条时，又要变更了呢？？
第3和第8条的上一级和该行记录的username都不相同啊

〇〇 · 发表于 2017-5-3 11:01

把每个人对应的最小pid求出来，然后把上级用户名与此关联，找到这个pid的上层

〇〇 · 发表于 2017-5-3 11:12

你把示例数据每一行需要查出的结果都贴出来

kylemo · 发表于 2017-5-3 12:09

抱歉啊，这是最终的结果：
ID PID TREENM                   USERNAME    USERNAME_C
1 1 TEST                李明                   李明
2 1 TEST\AA                   李华                李华
3 2 TEST\AA\BB          何思可          何思可
4 3 TEST\AA\BB\CC          何思可          李华
5 3 TEST\AA\BB\DD          吴小明       何思可
6 4 TEST\AA\BB\DD\EE 吴小明       何思可
7 4 TEST\AA\BB\DD\JJ11 吴小明       何思可
8 4 TEST\AA\BB\DD\EE22 吴小明       何思可

〇〇 · 发表于 2017-5-3 13:15

kylemo 发表于 2017-5-3 12:09
抱歉啊，这是最终的结果：
ID PID TREENM USERNAME USERNAME_C
1 1 TEST ...

3 2 TEST\AA\BB 何思可何思可
怎么解释

lastwinner · 发表于 2017-5-3 14:16

〇〇发表于 2017-5-3 13:15
3 2 TEST\AA\BB 何思可何思可
怎么解释

同问。

newkid · 发表于 2017-5-3 23:29

就是说，任何相邻两级的名字不能相同？
如果儿子和孙子同名，那么就把孙子改成爷爷名字？
如果曾孙子本来也是爷爷的名字，也就是曾孙子和孙子本来不同名，那么孙子改成爷爷以后变得和曾孙子同名了，怎么办？

kylemo · 发表于 2017-5-4 12:09

newkid 发表于 2017-5-3 23:29
就是说，任何相邻两级的名字不能相同？
如果儿子和孙子同名，那么就把孙子改成爷爷名字？
如果曾孙子本来 ...

1、任何相邻两级的名字不能相同是的
2、如果儿子和孙子同名，那么就把孙子改成爷爷名字？对
3、如果曾孙子本来也是爷爷的名字，也就是曾孙子和孙子本来不同名，那么孙子改成爷爷以后变得和曾孙子同名了，怎么办？不需要理会修改后的同名

westscott · 发表于 2017-5-4 14:55

不知道有没有理解错楼主的意思
WITH X AS
(SELECT '1' AS id, '' 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),
Y(
ID,PID,TREENM,USERNAME,USERNAME_C) AS
  (SELECT a.*,a.username username_c
from x a
where id=(SELECT min(pid) from x )
  UNION ALL
SELECT x.ID,x.PID,x.TREENM,x.USERNAME,case when y.username=x.USERNAME then  y.username_c else y.username end username_c
from y,x
WHERE y.id=x.pid)
SELECT * FROM y

[SQL] 求一个递归查询SQL