34,575
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
[name] NVARCHAR(10),
[manageid] INT
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES (1,'张三',2)
INSERT INTO t VALUES (2,'李四',3)
INSERT INTO t VALUES (3,'五五',4)
INSERT INTO t VALUES (4,'赵六',0)
--查所有人的所有上级,并插入到A表
--1.
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
--2.
;WITH cte AS (
SELECT id AS oid, * FROM t
UNION ALL
SELECT b.id AS oid, a.* FROM t AS a INNER JOIN cte AS b ON a.id=b.[manageid]
)
SELECT *
,STUFF((SELECT ','+ b.name FROM cte AS b WHERE a.id=b.oid AND a.id!=b.id FOR XML PATH('')),1,1,'') AS leaderNames
INTO A
FROM t AS a
SELECT * FROM A
/*
id name manageid leaderNames
----------- ---------- ----------- -------------------
1 张三 2 李四
2 李四 3 五五,五五
3 五五 4 赵六,赵六,赵六
4 赵六 0 NULL
*/
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(22),[manageid] int)
Insert #T
select 1,N'张三',2 union all
select 2,N'李四',3 union all
select 3,N'王五',4 union all
select 4,N'赵六',0
GO
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[name] nvarchar(22),[manageid] int)
--测试数据结束
;WITH cte AS (
Select * from #T WHERE name = '李四' --这里写的是想查谁的
UNION ALL
SELECT #T.* FROM #T JOIN cte ON cte.manageid = #T.id
)
INSERT INTO #A --插入数据
SELECT * FROM cte
SELECT * FROM #A --读取结果
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
GO
CREATE TABLE #t(
id INT PRIMARY KEY,
[name] NVARCHAR(10),
[manageid] INT
)
GO
SET NOCOUNT ON
INSERT INTO #t VALUES (1,'张三',2)
INSERT INTO #t VALUES (2,'李四',3)
INSERT INTO #t VALUES (3,'五五',4)
INSERT INTO #t VALUES (4,'赵六',0)
;WITH cte AS (
SELECT orgid=a.id,orgname=[name],a.*
FROM #t a
UNION ALL
SELECT b.orgid,b.orgname,a.*
FROM #t AS a
INNER JOIN cte AS b ON a.id=b.[manageid]
)
SELECT * FROM cte
order by orgid
orgid orgname id name manageid
----------- ---------- ----------- ---------- -----------
1 张三 1 张三 2
1 张三 2 李四 3
1 张三 3 五五 4
1 张三 4 赵六 0
2 李四 3 五五 4
2 李四 4 赵六 0
2 李四 2 李四 3
3 五五 3 五五 4
3 五五 4 赵六 0
4 赵六 4 赵六 0
IF OBJECT_ID('#t') IS NOT NULL DROP TABLE #t
GO
CREATE TABLE #t(
id INT PRIMARY KEY,
[name] NVARCHAR(10),
[manageid] INT
)
GO
SET NOCOUNT ON
INSERT INTO #t VALUES (1,'张三',2)
INSERT INTO #t VALUES (2,'李四',3)
INSERT INTO #t VALUES (3,'五五',4)
INSERT INTO #t VALUES (4,'赵六',0)
--查李四(id=2)的所有上级
;WITH cte AS (
SELECT orgid=a.id,a.*
FROM #t a
UNION ALL
SELECT b.orgid,a.*
FROM #t AS a
INNER JOIN cte AS b ON a.id=b.[manageid]
)
SELECT * FROM cte
order by orgid