使用SQL查询的树状视图 [英] Tree view using SQL Query

问题描述

我有一个区域表，我想要一个树视图(表简单地按树排序)是否可以使用sql查询获得帮助，我尝试使用自连接来做到这一点，但是我没有得到想要的结果.

I have a regions table of which I want a tree view (table simple ordered as tree) is it possible using sql queries help is appreciated, I tried to do it using self joins but i did not get the desired result.

树视图就是这样

个人

• Div1
• • Zon1
  • Div1
  • • Zon1
    • zon2
    • zon3

    根据查尔斯·布雷塔纳(Charles Bretana)的建议，我在下面的查询中尝试了CTE，但没有给我想要的结果.

    as per Charles Bretana suggetion I tried CTE in below query and it did not give me desired result.

    WITH Emp_CTE (id, ParentID, name)
    AS (
    SELECT id, ParentID, name
    FROM eQPortal_Region
    WHERE ParentID=0
    UNION ALL
    SELECT e.id, e.ParentID, e.name
    FROM eQPortal_Region e
    INNER JOIN Emp_CTE ecte ON ecte.id = e.ParentID
    )
    SELECT *
    FROM Emp_CTE
    GO
    

    这是结果..出了什么问题?

    This is the result .. what went wrong ?

    InDiv1

    • Div1
    • Div2
    • Div3
    • • Zon3
      • Div1
      • Div2
      • Div3
      • • Zon3
        • Zon2
        • zon1

        推荐答案

        这个人 Maulik Dhorajia 完美地回答了这个问题……

        This guy Maulik Dhorajia answers the question perfectly ...

        http://maulikdhorajia.blogspot.com/2012/06/sql-server-using-ctecommon-table.html

        制作查询的副本..

        ;WITH CTECompany
        AS
        (
            SELECT 
            ID, 
            ParentID, 
            Name , 
            0 AS HLevel,
            CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),ID),20) AS VARCHAR(MAX)) AS OrderByField
        FROM Region
        WHERE ParentID = 0
        
        UNION ALL
        
        SELECT 
            C.ID, 
            C.ParentID, 
            C.Name , 
            (CTE.HLevel + 1) AS HLevel,
            CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.ID),20) AS VARCHAR(MAX)) AS OrderByField
        FROM Region C
        INNER JOIN CTECompany CTE ON CTE.ID = C.ParentID
        WHERE C.ParentID IS NOT NULL
        
        
        )
        
        -- Working Example
        SELECT 
        ID
        , ParentID
        , HLevel
        , Name
        , (REPLICATE( '----' , HLevel ) + Name) AS Hierachy
        FROM CTECompany
        ORDER BY OrderByField
        

        这篇关于使用SQL查询的树状视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！