6

这个 SQL 有水平吗？巧妙利用 MySQL 用户变量查找层次数据库任意路径

 3 years ago
source link: https://www.v2ex.com/t/778486
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

V2EX  ›  程序员

这个 SQL 有水平吗？巧妙利用 MySQL 用户变量查找层次数据库任意路径

  huiyanpohundh123 · 2 天前 · 1865 次点击

发现公司代码库里面一个非常有水平(在我有限的见识里)的 SQL

层次数据库，数据库结构类似于这种，也是层次数据常用的写法了

create table t(
    id int primary key comment 'id',
    parent_id int comment 'parent_id'
)

目标是查找一个一个节点的全路径，例如
id,parent_id
1,0
2,1
3,1
4,1
5,2
输入参数 5 能输出
5
2
1

SELECT T2.id
FROM (
         SELECT @r                                                AS _id,
                (SELECT @r := parent_id FROM t WHERE id = _id) AS parent_id,
                @l := @l + 1                                      AS lvl
         FROM (SELECT @r := 5, @l := 0) vars,
              t h) T1
         JOIN t T2 ON T1._id = T2.id

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK