做了一个菜单的数据.现在通过权限显示.
ID Parent_id Name url
1 null main null
2 1 Node_1 null
3 2 Node_1_1 xxx
4 2 Node_1_2 xxx
5 2 Node_1_3 xxx
6 1 Node_2 null
7 7 Node_2_1 xxx
如果我这边传递出来的id是5和7
要照出来对应5和7的所有父节点数据,同时去掉重复,得到的数据如下ID Parent_id Name url
1 null main null
2 1 Node_1 null
5 2 Node_1_3 xxx
6 1 Node_2 null
7 7 Node_2_1 xxx需要如何操作?是分别循环然后union一起吗?
ID Parent_id Name url
1 null main null
2 1 Node_1 null
3 2 Node_1_1 xxx
4 2 Node_1_2 xxx
5 2 Node_1_3 xxx
6 1 Node_2 null
7 7 Node_2_1 xxx
如果我这边传递出来的id是5和7
要照出来对应5和7的所有父节点数据,同时去掉重复,得到的数据如下ID Parent_id Name url
1 null main null
2 1 Node_1 null
5 2 Node_1_3 xxx
6 1 Node_2 null
7 7 Node_2_1 xxx需要如何操作?是分别循环然后union一起吗?
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-11 15:42:22
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[Parent_id] int,[Name] varchar(8),[url] varchar(3))
insert [huang]
select 1,null,'main',null union all
select 2,1,'Node_1',null union all
select 3,2,'Node_1_1','xxx' union all
select 4,2,'Node_1_2','xxx' union all
select 5,2,'Node_1_3','xxx' union all
select 6,1,'Node_2',null union all
select 7,6,'Node_2_1','xxx'
--------------开始查询--------------------------
;WITH cte AS
(
select * from [huang]
WHERE id IN (5,7)
UNION ALL
SELECT a.*
FROM huang a INNER JOIN cte b ON a.id=b.[Parent_id])
SELECT DISTINCT * FROM cte----------------结果----------------------------
/*
ID Parent_id Name url
----------- ----------- -------- ----
1 NULL main NULL
2 1 Node_1 NULL
5 2 Node_1_3 xxx
6 1 Node_2 NULL
7 6 Node_2_1 xxx
*/
if object_id('tb') is not null drop table tb
go create table tb([ID] int,[Parent_id] int,[Name] varchar(8),[url] varchar(3))
insert tb
select 1,null,'main',null union all
select 2,1,'Node_1',null union all
select 3,2,'Node_1_1','xxx' union all
select 4,2,'Node_1_2','xxx' union all
select 5,2,'Node_1_3','xxx' union all
select 6,1,'Node_2',null union all
select 7,6,'Node_2_1','xxx'
;with t
as
(
select * from tb where ID in (5,7)union allselect tt.*
from t
inner join tb tt
on t.Parent_id = tt.ID
)select distinct *
from t
/*
ID Parent_id Name url
1 NULL main NULL
2 1 Node_1 NULL
5 2 Node_1_3 xxx
6 1 Node_2 NULL
7 6 Node_2_1 xxx
*/