T-SQL How to get all items from a tree in T-SQL?

问题:

I have a problem with a t-sql query.

Let's say I have a categories tree (categories ID)

cat_table

 1
 |
 2-\
 |  3-\
 6  |  5
 |  4  |
...   ...

ads_table

ad_ID
category_ID

of course the category_ID column references to the ID column in the cat_table

the problem is, how to get (recursive ?) all advertisements from all categories which the top-most parent is the 1st category?

回答1:

Are you familiar with Common Table Expressions in SQL Server? One of the many uses a CTE has is to do recursive queries.

The following is one of the best articles I've found on the subject:

http://www.4guysfromrolla.com/webtech/071906-1.shtml

回答2:

try using a recursive Common Table Expressions, aka "CTE" (available in SQL Server 2005 and up) like this:

--go through a nested table supervisor - user table and display the chain
DECLARE @Contacts table (id varchar(6), first_name varchar(10), reports_to_id varchar(6))
INSERT @Contacts VALUES ('1','Jerome', NULL )  -- tree is as follows:
INSERT @Contacts VALUES ('2','Joe'   ,'1')     --                      1-Jerome
INSERT @Contacts VALUES ('3','Paul'  ,'2')     --                     /        \
INSERT @Contacts VALUES ('4','Jack'  ,'3')     --              2-Joe           9-Bill
INSERT @Contacts VALUES ('5','Daniel','3')     --            /       \              \
INSERT @Contacts VALUES ('6','David' ,'2')     --     3-Paul          6-David       10-Sam
INSERT @Contacts VALUES ('7','Ian'   ,'6')     --    /      \            /    \
INSERT @Contacts VALUES ('8','Helen' ,'6')     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @Contacts VALUES ('9','Bill ' ,'1')     --
INSERT @Contacts VALUES ('10','Sam'  ,'9')     --

DECLARE @Root_id  char(4)

--get 2 and below
SET @Root_id=2
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

output:

@Root_id='2   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
2      Joe        1             1          Jerome             1
3      Paul       2             2          Joe                2
6      David      2             2          Joe                2
7      Ian        6             6          David              3
8      Helen      6             6          David              3
4      Jack       3             3          Paul               3
5      Daniel     3             3          Paul               3

(7 row(s) affected)

change @Root_id to get different output:

@Root_id=null
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
1      Jerome     NULL          NULL       NULL               1
2      Joe        1             1          Jerome             2
9      Bill       1             1          Jerome             2
10     Sam        9             9          Bill               3
3      Paul       2             2          Joe                3
6      David      2             2          Joe                3
7      Ian        6             6          David              4
8      Helen      6             6          David              4
4      Jack       3             3          Paul               4
5      Daniel     3             3          Paul               4

(10 row(s) affected)

回答3:

There is an option to avoid recurrency in tree browsing queries. You can add 'Path' column to your categories tree. It should contain each element ancestors IDs delimited with some non-numeric character (like slash).

For example your "ID=4" category's path would look like that: "/1/2/3/" Now when you join your ads table to categories you need to do following:

select * from ads_table 
inner join cat_table on cat_table.ID = ads_table.category_ID
where cat_table.Path like '/1/%'

And that's your query.

You can read more on this topic on my blog post