mysql无限极条件查询_关于使用sql语句实现无限极分类查询的疑问

如果只有这三级的话，可以这样自己和自己连表取

语句为:

select a.id as pid, a.name as pname, a.pid as ppid, b.name as ppname

from family a join family b on a.pid=b.id where a.id=儿子的信息的pid;

实例：

db83-3306>>select * from family;

+----+--------+------+

| id | name | pid |

+----+--------+------+

| 1 | 爷爷 | 0 |

| 2 | 父亲 | 1 |

| 3 | 儿子 | 2 |

| 4 | 女儿 | 2 |

+----+--------+------+

db83-3306>>select a.id as pid, a.name as pname, a.pid as ppid, b.name as ppname

-> from family a join family b on a.pid=b.id where a.id=2;

+-----+--------+------+--------+

| pid | pname | ppid | ppname |

+-----+--------+------+--------+

| 2 | 父亲 | 1 | 爷爷 |

+-----+--------+------+--------+

如果级数不知道，可以用类似下面这个语句查询, @r := 2为查询条件

SELECT T2.id, T2.name, T2.pid

FROM (

SELECT

@r AS _id,

(SELECT @r := pid FROM family WHERE id = _id) AS pid,

@l := @l + 1 AS l

FROM

(SELECT @r := 2, @l := 0) vars,

family h

WHERE @r != 0) T1

JOIN family T2

ON T1._id = T2.id

ORDER BY T1.l;

效果

db83-3306>>SELECT T2.id, T2.name, T2.pid

-> FROM (

-> SELECT

-> @r AS _id,

-> (SELECT @r := pid FROM family WHERE id = _id) AS pid,

-> @l := @l + 1 AS l

-> FROM

-> (SELECT @r := 2, @l := 0) vars,

-> family h

-> WHERE @r != 0) T1

-> JOIN family T2

-> ON T1._id = T2.id

-> ORDER BY T1.l;

+----+--------+------+

| id | name | pid |

+----+--------+------+

| 2 | 父亲 | 1 |

| 1 | 爷爷 | 0 |

+----+--------+------+

2 rows in set (0.00 sec)