SQL - 递归查询

Question

我有以下带有示例数据的mysql表格：

 id    location            parentid
 1     UK                  0
 2     East Anglia         1
 3     Cambridgeshire      2
 4     Norfolk             2
 5     Suffolk             2 
 6     East Midlands       1
 7     Derbyshire          6
 8     Leicestershire      6 
 9     EU Countries        0
 10    Austria             9
 11    Belgium             9

我希望生成一个查询，通过位置名称获取包括任何父级位置的位置列表。例如，

搜索“folk”应返回：

 id  location
 4   Norfolk, East Anglia, UK
 5   Suffolk, East Anglia, UK

搜索关键词 East 应返回以下内容：

id  location
2   East Anglia, UK
6   East Midlands, UK

搜索“Bel”应该返回以下结果：

id  location
11  Belgium 

在上述内容中，我们正在排除连接“欧盟国家”。

显然，以下内容不起作用：

select c.id, CONCAT_WS(', ', c.location, p.location, pp.location) as location
from tbl_locations c
   outer left join tbl_locations p on p.id = c.parentid
   outer left join tbl_locations pp on pp.id = p.parentid
 where c.location like '%whatever%'

- adam78

---

你可以编写一个函数，该函数返回给定locationid的文本。编辑：函数必须调用自身，直到没有需要调用的内容为止（这也是递归的定义）。 - Doruk

http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html - Robert Bain

最大深度是多少？ - sumit

@sumit，最好不要依赖于最大值。我猜它必须是递归的。 - Doruk

MySQL没有本地支持递归的功能。可选方案包括构建存储过程、将表自身连接多次以满足需求、在应用程序代码中处理逻辑，或者切换到不同的模型（参见嵌套集）。 - Strawberry

Answer 1

如果你只想要父级位置，可以使用自连接实现：

select c.id, c.location, p.id, p.location
from tbl_locations c
  outer left join tbl_locations p on p.id = c.parentid
where c.location like '%whatever%'

这可以通过外连接进行扩展，以达到任意级别，但查询将变得很长。例如，对于三个级别：

select c.id, c.location, p.id, p.location, pp.id, pp.location
from tbl_locations c
  outer left join tbl_locations p on p.id = c.parentid
  outer left join tbl_locations pp on pp.id = p.parentid
where c.location like '%whatever%'

更一般的递归查询取决于关系型数据库管理系统的详细信息。最简单的方法是使用公共表达式（CTE）。但是MySQL不支持它们（至少目前还不支持）。可以采用其他方法：在MySQL中从分层数据生成基于深度的树形结构（没有CTEs）。

- Richard

---

假设我有4个级别，那么以上内容会有巨大的性能影响吗？ - adam78

@adam78：一如既往，这取决于你所使用的数据集是否真实。即使是在详细级别（数千行）和索引id和parentid的情况下，我也预计它会非常快速地运行所有英国位置。但要确定其性能，唯一的方法就是使用真实数据进行测试，并考虑您将多频繁地使用此类查询。 - Richard

Answer 2

1) 没有标准的SQL查询可以计算传递关系的"传递闭包"。如果您想嵌套选择语句，您将始终达到最大深度。

2) 没有标准的SQL查询可以返回具有可变列数的行。因此，您必须以某种方式格式化结果（例如csv）。

但是，您可以使用存储过程在MySQL中实现：

 1 CREATE DATABASE IF NOT EXISTS test;
 2 USE test;
 3 
 4 
 5 DROP TABLE IF EXISTS location;
 6 CREATE TABLE location (id INT UNSIGNED PRIMARY KEY, name VARCHAR(30) NOT NULL, parent_id INT UNSIGNED NULL REFERENCES location(id));
 7 
 8 INSERT INTO location VALUES
 9 (1,"UK",0), 
10 (2,"East Anglia",1),
11 (3,"Cambridgeshire",2),
12 (4,"Norfolk",2),
13 (5,"Suffolk",2),
14 (6,"East Midlands",1),
15 (7,"Derbyshire",6),
16 (8,"Leicestershire",6);
17 
18 
19 
20 
21 DROP FUNCTION IF EXISTS location_with_parents;
22 DELIMITER //
23 CREATE FUNCTION location_with_parents(location_id INT UNSIGNED) RETURNS VARCHAR(255) READS SQL DATA
24 BEGIN
25     DECLARE LOC_STR VARCHAR(255) DEFAULT NULL;
26     DECLARE LOC_ADD VARCHAR(255) DEFAULT NULL;
27     DECLARE PAR_ID INT UNSIGNED DEFAULT location_id;
28     
29     SELECT name INTO LOC_STR FROM location where id=PAR_ID;
30     loop_label: LOOP 
31         SELECT parent_id INTO PAR_ID FROM location where id=PAR_ID;
32         
33         IF PAR_ID = 0 THEN
34             LEAVE loop_label;
35         ELSE
36             SELECT name INTO LOC_ADD FROM location where id=PAR_ID;
37             SET LOC_STR = CONCAT(LOC_STR, ', ', LOC_ADD); 
38             ITERATE loop_label;
39         END IF;
40     END LOOP loop_label;
41     RETURN LOC_STR;
42     
43 END;
44 //
45 
46 DELIMITER ;
47 
48 
49 
50 SELECT location_with_parents(id) FROM location WHERE name LIKE "%folk%";
51 
52 DROP DATABASE test;

在 MySQL 5.6.35 上对我有效。

希望这可以帮到你！

- nCessity

Answer 3

以下查询使用递归方法，可以给您提供精确的结果。

    Select S.ID , 
    concat( S.location,',', Group_concat
    (distinct A.location ORDER BY  A.location  SEPARATOR ',' ) ) as location
    from

         (    SELECT  distinct @r AS _id  ,location,
                        (
                        SELECT  @r := parentid
                        FROM    tbl_locations 
                        WHERE   id = _id
                        ) AS parentid,
                        @l := @l + 1 AS level
                FROM    (
                        SELECT  @r := h.ID,
                                @l := 0,
                                @cl := 0
                         from tbl_locations  h
                         where location like '%folk%'

                        ) vars,
                        tbl_locations  h
                WHERE   @r <> 0


          )A , tbl_locations    S
                where s.location like '%folk%'
                group by S.ID

OutPut :

 location like '%East%' :

location like '%Folk%'

这是一个好问题，请检查并询问是否有任何疑虑。

- Mr. Bhosale