递归查询西湖区上级所有城市
使用函数的方式进行递归查询出查询西湖区及上级所有城市,参考结果如下(西湖区ID:330106):
ID | NAME | PID |
---|---|---|
330000 | 浙江省 | 0 |
330100 | 杭州市 | 330000 |
330101 | 市辖区 | 330100 |
330106 | 西湖区 | 330101 |
参考答案:
SELECT
T2.id,
T2.name,
T2.pid
FROM
(
SELECT
@r AS _id,
( SELECT @r := pid FROM china WHERE id = _id ) AS pid,
@l := @l + 1 AS l
FROM
( SELECT @r := 330106, @l := 0 ) vars,
china h
WHERE
@r != 0
) T1
JOIN china T2 ON T1._id = T2.id
ORDER BY
T1.l DESC;