我有一个MySQL表，如下所示:
| id | name | parent_id |
|---|---|---|
| 19 | category1 | 0 |
| 20 | category2 | 19 |
| 21 | category3 | 20 |
| 22 | category4 | 21 |
| ... | ... | ... |
现在，我想有一个单一的MySQL查询，我只是提供id[例如说id=19]，然后我应该得到它的所有子id[即结果应该有id '20,21,22']....
孩子们的等级尚不清楚;它可以变化....
我知道如何使用for循环…但是如何使用一个MySQL查询来实现相同的功能呢?
当前回答
试试这些:
表定义:
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
parent_id INT,
CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
REFERENCES category (id)
) engine=innodb;
实验行:
INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);
存储过程:
DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
DECLARE catname VARCHAR(20);
DECLARE temppath TEXT;
DECLARE tempparent INT;
SET max_sp_recursion_depth = 255;
SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
IF tempparent IS NULL
THEN
SET path = catname;
ELSE
CALL getpath(tempparent, temppath);
SET path = CONCAT(temppath, '/', catname);
END IF;
END$$
DELIMITER ;
存储过程的包装器函数:
DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE res TEXT;
CALL getpath(cat_id, res);
RETURN res;
END$$
DELIMITER ;
选择的例子:
SELECT id, name, getpath(id) AS path FROM category;
输出:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1 |
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA |
| 24 | categoryB | category1/categoryA/categoryB |
| 25 | categoryC | category1/categoryA/categoryC |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+
过滤指定路径的行:
SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';
输出:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+
其他回答
我能想到的最好方法是
使用沿袭存储\排序\跟踪树。这已经足够了，而且阅读速度比其他任何方法都要快数千倍。 它还允许即使DB将改变也保持该模式(因为任何DB将允许使用该模式) 使用为特定ID确定谱系的函数。 您可以随心所欲地使用它(在选择中，或在CUD操作中，甚至按作业)。
谱系方法描述。可以在任何地方找到，例如 这里或者这里。 至于功能，这就是我的灵感所在。
在最后-得到或多或少简单，相对快速，简单的解决方案。
函数的身体
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
READS SQL DATA
BEGIN
DECLARE v_rec INT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE v_res text DEFAULT '';
DECLARE v_papa int;
DECLARE v_papa_papa int DEFAULT -1;
DECLARE csr CURSOR FOR
select _id,parent_id -- @n:=@n+1 as rownum,T1.*
from
(SELECT @r AS _id,
(SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := the_id, @l := 0,@n:=0) vars,
table m
WHERE @r <> 0
) T1
where T1.parent_id is not null
ORDER BY T1.lvl DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open csr;
read_loop: LOOP
fetch csr into v_papa,v_papa_papa;
SET v_rec = v_rec+1;
IF done THEN
LEAVE read_loop;
END IF;
-- add first
IF v_rec = 1 THEN
SET v_res = v_papa_papa;
END IF;
SET v_res = CONCAT(v_res,'-',v_papa);
END LOOP;
close csr;
return v_res;
END
然后你就
select get_lineage(the_id)
希望它能帮助到一些人:)
列出第一个递归的子元素的简单查询:
select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv
结果:
id name parent_id
20 category2 19
21 category3 20
22 category4 21
26 category24 22
．.． 左连接:
select
@pv:=p1.id as id
, p2.name as parent_name
, p1.name name
, p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv
@tincot列出所有孩子的解决方案:
select id,
name,
parent_id
from (select * from products
order by parent_id, id) products_sorted,
(select @pv := '19') initialisation
where find_in_set(parent_id, @pv) > 0
and @pv := concat(@pv, ',', id)
用Sql Fiddle在线测试并查看所有结果。
http://sqlfiddle.com/ !9 / a318e3/4/0
您可以在其他数据库中使用递归查询(性能上的YMMV)很容易地做到这一点。
另一种方法是存储两个额外的数据位，一个左值和一个右值。左值和右值来自于对所表示的树结构的预序遍历。
这就是所谓的Modified Preorder Tree遍历，允许您运行一个简单的查询来一次性获得所有父值。它也被称为“嵌套集”。
试试这些:
表定义:
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
parent_id INT,
CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
REFERENCES category (id)
) engine=innodb;
实验行:
INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);
存储过程:
DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
DECLARE catname VARCHAR(20);
DECLARE temppath TEXT;
DECLARE tempparent INT;
SET max_sp_recursion_depth = 255;
SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
IF tempparent IS NULL
THEN
SET path = catname;
ELSE
CALL getpath(tempparent, temppath);
SET path = CONCAT(temppath, '/', catname);
END IF;
END$$
DELIMITER ;
存储过程的包装器函数:
DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE res TEXT;
CALL getpath(cat_id, res);
RETURN res;
END$$
DELIMITER ;
选择的例子:
SELECT id, name, getpath(id) AS path FROM category;
输出:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1 |
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA |
| 24 | categoryB | category1/categoryA/categoryB |
| 25 | categoryC | category1/categoryA/categoryC |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+
过滤指定路径的行:
SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';
输出:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+
如果需要快速读取速度，最好的选择是使用闭包表。闭包表为每个祖先/后代对包含一行。在你的例子中，闭包表是这样的
ancestor | descendant | depth
0 | 0 | 0
0 | 19 | 1
0 | 20 | 2
0 | 21 | 3
0 | 22 | 4
19 | 19 | 0
19 | 20 | 1
19 | 21 | 3
19 | 22 | 4
20 | 20 | 0
20 | 21 | 1
20 | 22 | 2
21 | 21 | 0
21 | 22 | 1
22 | 22 | 0
一旦有了这个表，分层查询就变得非常简单和快速。获取类别20的所有子类:
SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0
当然，无论何时使用这样的非规格化数据都有一个很大的缺点。您需要在类别表旁边维护闭包表。最好的方法可能是使用触发器，但是正确跟踪闭包表的插入/更新/删除有点复杂。与任何事情一样，您需要查看您的需求，并决定哪种方法最适合您。
编辑:请参阅问题:在关系数据库中存储层次数据的选项是什么?更多选项。不同的情况有不同的最佳解决方案。
推荐文章
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 如何从命令行在windows中找到mysql数据目录
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
- 如何找到MySQL的根密码
- 如何改变字符集(和排序)在整个数据库?
- mySQL:: insert到表，数据从另一个表?
- 重复键忽略?
- 如何在Postgres中获得两个字段的MIN() ?
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 将表从一个数据库复制到另一个数据库的最简单方法?
- 如何在SQL中选择表的最后一条记录?
- SQL在Oracle中连接多行列值的查询
