設定admin為頂，同一行中的pname 介紹了use_name ， 請問 admin為起始 如何統計 admin介紹了多少層 ，pname 介紹了use_name 一次就為一層。 MYSQL 查詢陳述句如何寫
uj5u.com熱心網友回復：
with RECURSIVE tmp(user_name, p_name) as (select 'admin' as user_name, null as p_name from dual union all
select 'gs1' as user_name, 'admin' as p_name from dual union all
select 'gs2' as user_name, 'gs1' as p_name from dual union all
select 'gs3' as user_name, 'gs1' as p_name from dual union all
select 'gs4' as user_name, 'gs2' as p_name from dual union all
select 'cqc' as user_name, 'gs4' as p_name from dual union all
select 'bxp' as user_name, 'cqc' as p_name from dual),
tmp1(user_name, p_name, lvl, p_path) as(
select user_name, p_name, 0 as lvl, cast(user_name as char(200)) as p_path from tmp where p_name is null
union all
select t2.user_name, t2.p_name, t1.lvl + 1 as lvl, concat(t1.p_path, '->', t2.user_name) as path
from tmp1 t1 inner join tmp t2 on t1.user_name = t2.p_name)
select * from tmp1;
uj5u.com熱心網友回復：
https://dev.mysql.com/doc/refman/8.0/en/with.html 多看手冊，里面基本上都會有。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/57350.html
標籤：MySQL