declare @id int declare @pid int set @id = 3 select @pid = pid from tb where id = @idselect stuff((select ',' + ltrim(id) from tb where pid = @pid for xml path('')),1,1,'')[col]
啊 不好意思啊 。 还有点要求 ,在有个表连接 这个表 只有ID 这个字段。 ID 3 6 于上面那个的表连接 找到 各自 PID 下的 ID 然后输入一个表表结构行了,不用加 逗号的 呵呵
create table #t(ID INT, PID INT) INSERT #t SELECT 1 ,1 UNION ALL SELECT 2 ,1 UNION ALL SELECT 3 ,1 UNION ALL SELECT 4 ,4 UNION ALL SELECT 5 ,4 UNION ALL SELECT 6 ,4 UNION ALL SELECT 10 ,10SELECT REPLACE( (SELECT ID AS 'data()' FROM #t WHERE PID=(select PID from #t where id=3) FOR XML PATH('') ),' ',',') AS COL
COL ----------------- 1,2,3(1 行受影响)
create table #t(ID INT, PID INT) INSERT #t SELECT 1 ,1 UNION ALL SELECT 2 ,1 UNION ALL SELECT 3 ,1 UNION ALL SELECT 4 ,4 UNION ALL SELECT 5 ,4 UNION ALL SELECT 6 ,4 UNION ALL SELECT 10 ,10CREATE TABLE #T1(ID INT) INSERT #T1 SELECT 3 INSERT #T1 SELECT 6 SELECT A.*,P.* FROM #T1 A CROSS APPLY(SELECT (SELECT ID AS 'data()' FROM #t WHERE PID=(select PID from #t where id=A.ID) FOR XML PATH('') ) AS COL) P ID COL ----------- --------- 3 1 2 3 6 4 5 6(2 行受影响)这样?
表一: ID PID 1 1 2 1 3 1 4 4 5 4 6 4 10 10 表二: ID 3 6 链接后结果 ID 1 2 3 4 5 6
表一: ID PID 1 1 2 1 3 1 4 4 5 4 6 4 10 10 表二: ID 3 6 链接后结果 ID 1 2 3 4 5 6
select id from t1 where pid in (select a.pid from t1 as a join t2 as b on a.id = b.id)
create table t1(ID INT, PID INT) INSERT t1 SELECT 1 ,1 UNION ALL SELECT 2 ,1 UNION ALL SELECT 3 ,1 UNION ALL SELECT 4 ,4 UNION ALL SELECT 5 ,4 UNION ALL SELECT 6 ,4 UNION ALL SELECT 10 ,10CREATE TABLE t2(ID INT) INSERT t2 SELECT 3 INSERT t2 SELECT 6 goselect id from t1 where pid in (select a.pid from t1 as a join t2 as b on a.id = b.id)drop table t1,t2 /*id ----------- 1 2 3 4 5 6(6 行受影响)
---2005用CTE ---2000用函数USE tempdb GO-- 建立演示环境 CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20)) INSERT Dept SELECT 0, 0, N'<全部>' UNION ALL SELECT 1, 0, N'财务部' UNION ALL SELECT 2, 0, N'行政部' UNION ALL SELECT 3, 0, N'业务部' UNION ALL SELECT 4, 0, N'业务部' UNION ALL SELECT 5, 4, N'销售部' UNION ALL SELECT 6, 4, N'MIS' UNION ALL SELECT 7, 6, N'UI' UNION ALL SELECT 8, 6, N'软件开发' UNION ALL SELECT 9, 8, N'内部开发' GO-- 查询指定部门下面的所有部门 DECLARE @Dept_name nvarchar(20) SET @Dept_name = N'MIS' ;WITH DEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id ) SELECT * FROM DEPTS GO
-->Title:Generating test data -->Author:wufeng4552 -->Date :2009-09-30 08:52:38 set nocount on if object_id('tb','U')is not null drop table tb go create table tb(ID int, ParentID int) insert into tb select 1,0 insert into tb select 2,1 insert into tb select 3,1 insert into tb select 4,2 insert into tb select 5,3 insert into tb select 6,5 insert into tb select 7,6 -->Title:查找指定節點下的子結點 if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID go create function Uf_GetChildID(@ParentID int) returns @t table(ID int) as begin insert @t select ID from tb where ParentID=@ParentID while @@rowcount<>0 begin insert @t select a.ID from tb a inner join @t b on a.ParentID=b.id and not exists(select 1 from @t where id=a.id) end return end go select * from dbo.Uf_GetChildID(5) /* ID ----------- 6 7 */ -->Title:查找指定節點的所有父結點 if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID go create function Uf_GetParentID(@ID int) returns @t table(ParentID int) as begin insert @t select ParentID from tb where ID=@ID while @@rowcount!=0 begin insert @t select a.ParentID from tb a inner join @t b on a.id=b.ParentID and not exists(select 1 from @t where ParentID=a.ParentID) end return end go select * from dbo.Uf_GetParentID(2) /* ParentID ----------- 1 0 */本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
declare @id int
declare @pid int
set @id = 3
select @pid = pid from tb where id = @idselect stuff((select ',' + ltrim(id) from tb where pid = @pid for xml path('')),1,1,'')[col]
啊 不好意思啊 。
还有点要求 ,在有个表连接 这个表 只有ID 这个字段。
ID
3
6
于上面那个的表连接 找到 各自 PID 下的 ID 然后输入一个表表结构行了,不用加 逗号的 呵呵
create table #t(ID INT, PID INT)
INSERT #t
SELECT 1 ,1 UNION ALL
SELECT 2 ,1 UNION ALL
SELECT 3 ,1 UNION ALL
SELECT 4 ,4 UNION ALL
SELECT 5 ,4 UNION ALL
SELECT 6 ,4 UNION ALL
SELECT 10 ,10SELECT REPLACE(
(SELECT
ID AS 'data()'
FROM #t WHERE PID=(select PID from #t where id=3)
FOR XML PATH('') ),' ',',') AS COL
COL
-----------------
1,2,3(1 行受影响)
create table #t(ID INT, PID INT)
INSERT #t
SELECT 1 ,1 UNION ALL
SELECT 2 ,1 UNION ALL
SELECT 3 ,1 UNION ALL
SELECT 4 ,4 UNION ALL
SELECT 5 ,4 UNION ALL
SELECT 6 ,4 UNION ALL
SELECT 10 ,10CREATE TABLE #T1(ID INT)
INSERT #T1
SELECT 3
INSERT #T1
SELECT 6
SELECT A.*,P.* FROM #T1 A
CROSS APPLY(SELECT
(SELECT
ID AS 'data()'
FROM #t WHERE PID=(select PID from #t where id=A.ID)
FOR XML PATH('') ) AS COL) P
ID COL
----------- ---------
3 1 2 3
6 4 5 6(2 行受影响)这样?
ID PID
1 1
2 1
3 1
4 4
5 4
6 4
10 10
表二:
ID
3
6
链接后结果
ID
1
2
3
4
5
6
ID PID
1 1
2 1
3 1
4 4
5 4
6 4
10 10
表二:
ID
3
6
链接后结果
ID
1
2
3
4
5
6
select id
from t1
where pid in (select a.pid from t1 as a join t2 as b on a.id = b.id)
create table t1(ID INT, PID INT)
INSERT t1
SELECT 1 ,1 UNION ALL
SELECT 2 ,1 UNION ALL
SELECT 3 ,1 UNION ALL
SELECT 4 ,4 UNION ALL
SELECT 5 ,4 UNION ALL
SELECT 6 ,4 UNION ALL
SELECT 10 ,10CREATE TABLE t2(ID INT)
INSERT t2
SELECT 3
INSERT t2
SELECT 6
goselect id
from t1
where pid in (select a.pid from t1 as a join t2 as b on a.id = b.id)drop table t1,t2
/*id
-----------
1
2
3
4
5
6(6 行受影响)
---2000用函数USE tempdb
GO-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept
WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx