34,590
社区成员
发帖
与我相关
我的任务
分享
select * from News_LCType
select ID,ParentID from News_LCType where id = 1 UNION ALL
select ID,ParentID from News_LCType where ParentID = 1 UNION ALL
select ID,ParentID from News_LCType where id = 3 UNION ALL
select ID,ParentID from News_LCType where ParentID = 3
;with cte as
(
select * from News_LCType where parentid = 0
union all
select * from News_LCType as a join cte on a.parentid=cte.id
)
select * from cte
典型在树形排序:
create table #t(id int,parentid varchar(10))
insert into #t
select 1, 0 union all
select 3 ,0union all
select 4 ,3union all
select 5 ,3union all
select 6 ,1
--查询处理的代码
SELECT *
FROM #t a
LEFT JOIN #t b ON a.parentid=b.id
ORDER BY CASE
WHEN b.parentid IS NULL THEN a.id
WHEN b.parentid=0 THEN b.id
ELSE b.parentid END,
CASE
WHEN b.parentid=0 THEN a.id
ELSE a.parentid END,
a.id