我有一个名为committed的表,其中主键是commit_no
,自引用列是commit_rollup_no
。我的目标是能够根据最高的commit_no
拉回所有参考表。
commit_no | commit_rollup_no
---------------------------
1 null
2 1
3 1
4 2
5 3
6 5
7 6
预期的结果:
所有提交到commit_no 1
2,3,4,5,6,7
所有提交到commit_no 2
4
所有提交到commit_no 3
5,6,7
我想知道有更多经验的人如何使用自引用表来处理这个查询。在某些情况下,它可能深入到100层,并且要求返回所有相关的承诺。
这样行吗?
SELECT `commit_no`,
(SELECT Group_concat(`commit_no`)
FROM `commits` `c`
WHERE `c`.`commit_rollup_no` = `com`.`commit_no`) AS `SubCommits`
FROM `commits` AS `com`
SQL小提琴MySQL 5.6 Schema Setup:
CREATE TABLE commits
(`commit_no` int, `commit_rollup_no` int)
;
INSERT INTO commits
(`commit_no`, `commit_rollup_no`)
VALUES
(1, 0),
(2, 1),
(3, 1),
(4, 2),
(5, 3),
(6, 5),
(7, 6)
;
查询1 :
SELECT `commit_no`, (SELECT GROUP_CONCAT(`commit_no`) FROM `commits` `c` WHERE `c`.`commit_rollup_no` = `com`.`commit_no`) AS `SubCommits` FROM `commits` AS `com`
结果:
+-----------+------------+
| commit_no | SubCommits |
+-----------+------------+
| 1 | 2,3 |
| 2 | 4 |
| 3 | 5 |
| 4 | (null) |
| 5 | 6 |
| 6 | 7 |
| 7 | (null) |
+-----------+------------+
CREATE DEFINER=`IDPAdmin`@`%` FUNCTION `CommitChildren`(ParentCommit INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE rv,q,queue,queue_children VARCHAR(1024);
DECLARE queue_length,front_id,pos INT;
SET rv = '';
SET queue = ParentCommit;
SET queue_length = 1;
WHILE queue_length > 0 DO
IF queue_length = 1 THEN
SET front_id = queue;
SET queue = '';
ELSE
SET front_id = SUBSTR(queue,1,LOCATE(',',queue)-1);
SET pos = LOCATE(',',queue) + 1;
SET q = SUBSTR(queue,pos);
SET queue = q;
END IF;
SET queue_length = queue_length - 1;
SELECT IFNULL(qc,'') INTO queue_children
FROM (SELECT GROUP_CONCAT(commit_no) qc
FROM commitments WHERE commit_rollup_no = front_id) A;
IF LENGTH(queue_children) = 0 THEN
IF LENGTH(queue) = 0 THEN
SET queue_length = 0;
END IF;
ELSE
IF LENGTH(rv) = 0 THEN
SET rv = queue_children;
ELSE
SET rv = CONCAT(rv,',',queue_children);
END IF;
IF LENGTH(queue) = 0 THEN
SET queue = queue_children;
ELSE
SET queue = CONCAT(queue,',',queue_children);
END IF;
SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
END IF;
END WHILE;
RETURN rv;
END