gpt4 book ai didi

mysql - SELECT 查询递归表,其名称来自 MYSQL 中的另一个表

转载 作者:行者123 更新时间:2023-11-29 11:39:43 25 4
gpt4 key购买 nike

我有两个表:

  • 第一:(id名称)
  • 第二个(递归,用于分层目的):(id, idsub)

如何显示每个 id 和 idsub 的名称?

最佳答案

要显示 4 深度层次结构,您可以在 second_table 上使用自联接,并从 first_table 上的子查询获取名称。你可以尝试这样的事情。

SELECT 
(SELECT f.names FROM first_table AS f WHERE f.id=s1.id) AS lvl1_names,
(SELECT f.names FROM first_table AS f WHERE f.id=s2.id) AS lvl2_names,
(SELECT f.names FROM first_table AS f WHERE f.id=s3.id) AS lvl3_names,
(SELECT f.names FROM first_table AS f WHERE f.id=s4.id) AS lvl4_names
FROM second_table AS s1
LEFT JOIN second_table AS s2 ON s1.id = s2.idsub
LEFT JOIN second_table AS s3 ON s2.id = s3.idsub
LEFT JOIN second_table AS s4 ON s3.id = s4.idsub

如果你有更简单的数据结构,例如first_table(id,names,parent)那么你可以简化它并避免子查询。像这样的东西。

SELECT 
f1.names AS lvl1_names,
f2.names AS lvl2_names,
f2.names AS lvl3_names,
f3.names AS lvl4_names
FROM first_table AS f1
LEFT JOIN first_table AS f2 ON f1.parent = f2.id
LEFT JOIN first_table AS f3 ON f2.parent = f3.id
LEFT JOIN first_table AS f4 ON f3.parent = f4.id

关于mysql - SELECT 查询递归表,其名称来自 MYSQL 中的另一个表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36034122/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com