mysql - 将表连接到自身？您能否建议一个最快的查询来获取这些结果？

Question

我有一个像这样的分类表:

+-----+--------------+----------------+--------------------+
| id  | name         | is_subcategory | parent_taxonomy_id |
+-----+--------------+----------------+--------------------+
|  80 | Headword     |              0 |                  0 |
|  81 | blonde       |              1 |                 80 |
|  82 | Parents      |              0 |                  0 |
|  83 | Children     |              1 |                 82 |
|  84 | Season       |              0 |                  0 |
|  85 | Winter       |              1 |                 84 |
|  86 | Charm Units  |              0 |                  0 |
|  88 | Etymology    |              1 |                 86 |
|  89 | Word History |              1 |                 86 |
|  90 | Spring       |              1 |                 84 |
|  91 | Summer       |              1 |                 84 |
|  93 | Trends       |              0 |                  0 |
| 109 | Interest     |              0 |                  0 |
| 110 | Sports       |              1 |                109 |
| 111 | Cups         |              0 |                  0 |
| 112 | hot          |              1 |                111 |
| 113 | Speakers     |              0 |                  0 |
| 114 | Hi def       |              1 |                113 |
| 115 | Dual powered |              1 |                113 |
| 118 | Office       |              0 |                  0 |
| 124 | States       |              0 |                  0 |
+-----+--------------+----------------+--------------------+

我需要从自动完成功能中获取所有类别和子类别的列表，例如，如果用户输入 sp - 他应该获得此列表:

Interest/Sports
Season/Spring
Speakers
Speakers/Dual powered
Speakers/Hi def

我是否正确 - 我应该进行自加入？我如何获得这种格式的结果？

编辑:

这是我的查询，似乎有效:

SELECT a.id AS cat_id, b.id AS subcat_id, a.name AS cat_name, b.name AS sub_cat_name, CONCAT_WS(' / ', a.name, b.name) AS full_name 
FROM taxonomies as a
LEFT JOIN taxonomies AS b ON a.id = b.parent_taxonomy_id
WHERE (a.name LIKE 'sp%' OR b.name LIKE 'sp%')
ORDER BY full_name DESC;

Answer 1

考虑以下因素:

DROP TABLE my_table;

CREATE TABLE my_table
(id  INT NOT NULL PRIMARY KEY
,name         VARCHAR(20) NOT NULL UNIQUE
,parent_taxonomy_id INT NULL
);

INSERT INTO my_table VALUES
( 80,'Headword',NULL),
( 81,'blonde',80),
( 82,'Parents',NULL),
( 83,'Children',82),
( 84,'Season',NULL),
( 85,'Winter',84),
( 86,'Charm Units',NULL),
( 88,'Etymology',86),
( 89,'Word History',86),
( 90,'Spring',84),
( 91,'Summer',84),
( 93,'Trends',NULL),
(109,'Interest',NULL),
(110,'Sports',109),
(111,'Cups',NULL),
(112,'hot',111),
(113,'Speakers',NULL),
(114,'Hi def',113),
(115,'Dual powered',113),
(118,'Office',NULL),
(124,'States',NULL);

SELECT n.*
  FROM 
     ( SELECT x.id x_id
            , x.name x_name
            , y.id y_id
            , y.name y_name
         FROM my_table x 
         LEFT 
         JOIN my_table y 
           ON y.parent_taxonomy_id = x.id 
        WHERE x.parent_taxonomy_id IS NULL
     ) n 
 WHERE (x_name LIKE 'sp%' OR y_name LIKE 'sp%');

+------+----------+------+--------------+
| x_id | x_name   | y_id | y_name       |
+------+----------+------+--------------+
|   84 | Season   |   90 | Spring       |
|  109 | Interest |  110 | Sports       |
|  113 | Speakers |  114 | Hi def       |
|  113 | Speakers |  115 | Dual powered |
+------+----------+------+--------------+