declare @table table
(
id int ,
name nvarchar(10),
a varchar(10),
superior_id int
)
insert @table
select 266, N'河北', 002, 1 union all
select 282, N'长沙', 001, 1 union all
select 503, N'石家庄', 345, 266id name a superior_id
266 河北 002 1
282 长沙 001 1
503 石家庄 345 266
输出
266 河北 002 1
503 石家庄 345 266
查询出河北及所属的机构,关联字段为266?上个帖子没说明白
(
id int ,
name nvarchar(10),
a varchar(10),
superior_id int
)
insert @table
select 266, N'河北', 002, 1 union all
select 282, N'长沙', 001, 1 union all
select 503, N'石家庄', 345, 266id name a superior_id
266 河北 002 1
282 长沙 001 1
503 石家庄 345 266
输出
266 河北 002 1
503 石家庄 345 266
查询出河北及所属的机构,关联字段为266?上个帖子没说明白
SELECT * FROM @TABLE WHERE ID = 266
UNION ALL
SELECT A.* FROM @TABLE A,CTE AS T
WHERE A.superior_id = T.ID
)
SELECT * FROM CTE
(
id int ,
name nvarchar(10),
a varchar(10),
superior_id int
)
insert @table
select 266, N'河北', 002, 1 union all
select 282, N'长沙', 001, 1 union all
select 503, N'石家庄', 345, 266;WITH CTE AS (
SELECT * FROM @TABLE WHERE ID = 266
UNION ALL
SELECT A.* FROM @TABLE A,CTE AS T
WHERE A.superior_id = T.ID
)
SELECT * FROM CTE--结果
266 河北 2 1
503 石家庄 345 266
(
id int ,
name nvarchar(10),
a varchar(10),
superior_id int
)
insert @table
select 266, N'河北', 002, 1 union all
select 282, N'长沙', 001, 1 union all
select 503, N'石家庄', 345, 266SELECT * FROM @TABLE WHERE ID = 266
UNION ALL
SELECT A.* FROM @TABLE A
WHERE A.superior_id = 266--结果
266 河北 2 1
503 石家庄 345 266
2层
(
id int ,
name nvarchar(10),
a varchar(10),
superior_id int
)
insert @table
select 266, N'河北', 002, 1 union all
select 282, N'长沙', 001, 1 union all
select 503, N'石家庄', 345, 266
select * from @table where id = 266
union
select * from @table t where exists
(select 1 from @table where id = t.superior_id and t.superior_id = 266)
(
id int ,
name nvarchar(10),
a varchar(10),
superior_id int
)
insert @table
select 266, N'河北', 002, 1 union all
select 282, N'长沙', 001, 1 union all
select 503, N'石家庄', 345, 266 union all
select 603, N'石家庄A区', 345, 503SELECT * FROM @TABLE WHERE ID = 266
UNION ALL
SELECT A.* FROM @TABLE A
WHERE A.superior_id = 266
UNION ALL
SELECT A.* FROM @TABLE A,@TABLE B
WHERE A.superior_id = B.id
AND B.superior_id = 266--结果
id name a superior_id
266 河北 2 1
503 石家庄 345 266
603 石家庄A区 345 503
3层,函数写法就懒得写了,函数不是好东西