查询列出分层数据中的所有路径

发布于 2024-11-26 02:56:37 字数 340 浏览 1 评论 0原文

我已经创建了表并在其中存储了分层数据。该表与此答案完全相同。也有一些检索查询。

但我想列出表中所有可能的路径。查询内容是什么？

输出应该是 -

ROOT
ROOT/Dir2
ROOT/Dir3
ROOT/Dir4
ROOT/Dir5
ROOT/Dir3/Dir6
ROOT/Dir5/Dir7

....（顺序无关紧要）

原文

I have created the table and stored the hierarchical data in it. The table is exactly as given this answer. There are some queries for retrieval too.

But i want to list all possible paths from the tables. What is the query ?

The output should be -

ROOT
ROOT/Dir2
ROOT/Dir3
ROOT/Dir4
ROOT/Dir5
ROOT/Dir3/Dir6
ROOT/Dir5/Dir7

.... (Order does not matter)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鹤仙姿 2024-12-03 02:56:37

您想通过重复向查询添加左连接来伪造无限的深度或高度。只有当您确切知道树的高度或给自己设定一个限制时，这才有效。

SELECT V.uid UID, V.tiergruppe_id, V1.title Tiergruppe, V.nachweisart_id, V2.title Nachweisart, V.verhalten_id, V3.title Verhalten, V.quelle_id, V4.title Quelle, V.toleranz_id, V5.title Toleranz, V.tag_id, V6.title Tag, V.monat_id, V7.title Monat, V.jahr_id, V8.title Jahr, V.stunde_id, V9.title Stunde, V.minute_id, V10.title Minute, V.von_id, V11.title Von, V.bis_id, V12.title Bis, V.tageszeit_id, V13.title Tageszeit, V.spez_anzahl_id, V14.title Spez_Anz, V.gesamtanzahl Gesamtanzahl, V.amt Amt, V.awt Awt, V.gesamta Gesamta, V.gesamti Gesamti, V.anmerkungen Anmerkungen
        FROM tx_chsffoeag_fundmeldung V INNER JOIN tx_chsffoeag_fund V1 ON V.tiergruppe_id = V1.uid INNER JOIN tx_chsffoeag_fund V2 ON V.nachweisart_id = V2.uid INNER JOIN tx_chsffoeag_fund V3 ON V.verhalten_id = V3.uid INNER JOIN tx_chsffoeag_fund V4 ON V.quelle_id = V4.uid INNER JOIN tx_chsffoeag_fund V5 ON V.toleranz_id = V5.uid INNER JOIN tx_chsffoeag_fund V6 ON V.tag_id = V6.uid INNER JOIN tx_chsffoeag_fund V7 ON V.monat_id = V7.uid INNER JOIN tx_chsffoeag_fund V8 ON V.jahr_id = V8.uid INNER JOIN tx_chsffoeag_fund V9 ON V.stunde_id = V9.uid INNER JOIN tx_chsffoeag_fund V10 ON V.minute_id = V10.uid INNER JOIN tx_chsffoeag_fund V11 ON V.von_id = V11.uid INNER JOIN tx_chsffoeag_fund V12 ON V.bis_id = V12.uid INNER JOIN tx_chsffoeag_fund V13 ON V.tageszeit_id = V13.uid INNER JOIN tx_chsffoeag_fund V14 ON V.spez_anzahl_id = V14.uid
        WHERE
            V.deleted=0 AND V.hidden=0 AND V1.deleted=0 AND V1.hidden=0

You want to fake an unlimited depth or height by repeatley adding left joins to your query. This can only work if you know exactly the height of your tree or you set yourself a limit.

SELECT V.uid UID, V.tiergruppe_id, V1.title Tiergruppe, V.nachweisart_id, V2.title Nachweisart, V.verhalten_id, V3.title Verhalten, V.quelle_id, V4.title Quelle, V.toleranz_id, V5.title Toleranz, V.tag_id, V6.title Tag, V.monat_id, V7.title Monat, V.jahr_id, V8.title Jahr, V.stunde_id, V9.title Stunde, V.minute_id, V10.title Minute, V.von_id, V11.title Von, V.bis_id, V12.title Bis, V.tageszeit_id, V13.title Tageszeit, V.spez_anzahl_id, V14.title Spez_Anz, V.gesamtanzahl Gesamtanzahl, V.amt Amt, V.awt Awt, V.gesamta Gesamta, V.gesamti Gesamti, V.anmerkungen Anmerkungen
        FROM tx_chsffoeag_fundmeldung V INNER JOIN tx_chsffoeag_fund V1 ON V.tiergruppe_id = V1.uid INNER JOIN tx_chsffoeag_fund V2 ON V.nachweisart_id = V2.uid INNER JOIN tx_chsffoeag_fund V3 ON V.verhalten_id = V3.uid INNER JOIN tx_chsffoeag_fund V4 ON V.quelle_id = V4.uid INNER JOIN tx_chsffoeag_fund V5 ON V.toleranz_id = V5.uid INNER JOIN tx_chsffoeag_fund V6 ON V.tag_id = V6.uid INNER JOIN tx_chsffoeag_fund V7 ON V.monat_id = V7.uid INNER JOIN tx_chsffoeag_fund V8 ON V.jahr_id = V8.uid INNER JOIN tx_chsffoeag_fund V9 ON V.stunde_id = V9.uid INNER JOIN tx_chsffoeag_fund V10 ON V.minute_id = V10.uid INNER JOIN tx_chsffoeag_fund V11 ON V.von_id = V11.uid INNER JOIN tx_chsffoeag_fund V12 ON V.bis_id = V12.uid INNER JOIN tx_chsffoeag_fund V13 ON V.tageszeit_id = V13.uid INNER JOIN tx_chsffoeag_fund V14 ON V.spez_anzahl_id = V14.uid
        WHERE
            V.deleted=0 AND V.hidden=0 AND V1.deleted=0 AND V1.hidden=0

回复收藏 0 原文

~没有更多了~