mysql根据父id查询_sql 怎么根据父id查询下三级子集？

取决于你用什么数据库。看问题的标签里有个MySQL，就以MySQL为例。

MySQL目前还不支持Common Table Expressions，所以递归查询做不到。假如你预先知道你需要查询任意节点以下最多三层的数据，那倒也好办。无非多做几次JOIN而已。

假设表数据：

select * from sometable;

id name parent_id

1 01 0

2 02 1

3 03 2

4 04 3

5 05 3

6 06 4

7 07 6

你需要这么几层View

第0层：

CREATE VIEW `v0` AS

SELECT

`t`.`id` AS `id`,

`t`.`name` AS `name`,

`t`.`parent_id` AS `parent_id`,

0 AS `level`,

`t`.`id` AS `subtree_root_id`

FROM

`sometable` `t`

第1层：

CREATE VIEW `v1` AS

SELECT

`t`.`id` AS `id`,

`t`.`name` AS `name`,

`t`.`parent_id` AS `parent_id`,

(`v`.`level` + 1) AS `level`,

`v`.`subtree_root_id` AS `subtree_root_id`

FROM

(`sometable` `t`

JOIN `v0` `v` ON ((`t`.`parent_id` = `v`.`id`)))

UNION SELECT

`v`.`id` AS `id`,

`v`.`name` AS `name`,

`v`.`parent_id` AS `parent_id`,

`v`.`level` AS `level`,

`v`.`subtree_root_id` AS `subtree_root_id`

FROM

`v0` `v`

后面的几层都跟第1层一样做。思路就是

V(n+1) :=

T JOIN V(n) // produces level n + 1

UNION

V(n) // produces levels from 0 to level n

最后你只需要

select * from v3

where subtree_root_id = 3

order by level, id;

如果你不能建View，那么有两种做法。一种是把这3层view原地展开，得到一句丧心病狂的SQL：

SELECT * FROM (

SELECT

`t`.`id` AS `id`,

`t`.`name` AS `name`,

`t`.`parent_id` AS `parent_id`,

(`v2`.`level` + 1) AS `level`,

`v2`