表A
orgid orgname Parentid1 AA 1
2 BB 1
3 CC 2
4 DD 2
.......查询结果为 orgid orgname name name1
3 BB-CC BB CC 4 BB-DD BB DD
.....请问SQL语句怎么写?
orgid orgname Parentid1 AA 1
2 BB 1
3 CC 2
4 DD 2
.......查询结果为 orgid orgname name name1
3 BB-CC BB CC 4 BB-DD BB DD
.....请问SQL语句怎么写?
orgid orgname Parentid 1 AA 1
2 BB 1
3 CC 2
4 DD 2
5 EE 2
6 FF 2
7 HH 2
....... 查询结果为 orgid orgname name name1
3 BB-CC BB CC
4 BB-DD BB DD
5 BB-EE BB EE
6 BB-FF BB FF
7 BB-HH BB HH
.....
如题
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-- 删除演示环境
DROP TABLE Dept
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](orgid int, orgname varchar(2),Parentid int)
insert [tb] select
1, 'AA' ,1 union all select
2, 'BB' , 1 union all select
3, 'CC' , 2 union all select
4, 'DD' , 2 union all select
5, 'EE' , 2 union all select
6, 'FF' , 2 union all select
7 , 'HH', 2 select b.orgid,orgname=a.orgname+'-'+b.orgname,a.orgname as name, b.orgname as name1
from tb b,tb a
where b.parentid=2 and b.parentid=a.orgidorgid orgname name name1
----------- ------- ---- -----
3 BB-CC BB CC
4 BB-DD BB DD
5 BB-EE BB EE
6 BB-FF BB FF
7 BB-HH BB HH(5 行受影响)