SQL Server: Select Parent-Child

uajslkp6 于 11个月前发布在 SQL Server

关注(0)|答案(2)|浏览(172)

I have SQL Server 2008 with a table called ProductCategories designed like this:

Id | Name      | ParentId
71   PCs         NULL
32   MACs        NULL
 3   Keyboard    1
 9   Mouse       1
 5   Screen      1
11   Keyboard    2
 7   Mouse       2
 8   Screen      2

I would like to select from this table, and get a result set like this:

Id | Name      | ParentId
71   PCs         NULL
 3   Keyboard    1
 9   Mouse       1
 5   Screen      1
32   MACs        NULL
11   Keyboard    2
 7   Mouse       2
 8   Screen      2

I tried this, but that obviously gives me the ones with no ParentId first:

WITH Hierarchy
AS
(
    SELECT 
        T1.Id, T1.ParentId
    FROM
        ProductCategories T1
    WHERE 
        T1.parentid IS NULL OR 
        T1.parentid IN (SELECT id from ProductCategories WHERE parentid IS NULL)
    UNION ALL
    SELECT 
        T1.Id, T1.ParentId
    FROM
        ProductCategories T1
    INNER JOIN 
        Hierarchy TH ON TH.Id = T1.ParentId
)
select *
from Hierarchy 
order by parentid

Please help me, if you can :)

-- The guy who doesn't know SQL

sql-server

来源：https://stackoverflow.com/questions/1399708/sql-server-select-parent-child

2条答案

按热度按时间

siotufzp1#

try this:

Select Id, Name, ParentId
From ProductCategories
Order By Coalesce(ParentId, Id), 
   Coalesce(ParentId, 0), Name

Three Order By predicates,

Coalesce(ParentId, Id): This one groups the records by the parent, for both the parent itself and all the children of that parent
Coalesce(ParentId, 0) This groups within each set so that the one record with a null parent (the parent) sorts to the top within the group
Name, This sorts the children within the group by name

赞(0）回复(0）举报 11个月前

lokaqttq2#

Try this

SELECT id, name, parentId 
FROM categories
ORDER BY ISNULL(parentId,id), id

Btw, shouldn't first two indexes in your table be 1 and 2, not 71 and 32 ?

赞(0）回复(0）举报 11个月前

我来回答

SQL Server: Select Parent-Child

2条答案

相关问题

热门标签

最新问答