我的表结构(menu表)
id name fatherid
1 a <null>
2 b 1
3 c 2
4 d 1
5 f <null>
6 g 5
7 i 5
8 new 7
9 d 4
查询id=1 的记录及它下层的记录,要求得到结果为
id name fatherid
1 a <null>
2 b 1
3 c 2
4 d 1
9 d 4
...全文
2258打赏收藏
简单sql查询,高分求助(层次化查询)
我的表结构(menu表) id name fatherid 1 a 2 b 1 3 c 2 4 d 1 5 f 6 g 5 7 i 5 8 new 7 9 d 4 查询id=1 的记录及它下层的记录,要求得到结果为 id name fatherid 1 a 2 b 1 3 c 2 4 d 1 9 d 4
-- 测试数据
CREATE TABLE menu(id int, name varchar(10), fatherid int)
INSERT menu
SELECT 1, 'a', null
UNION ALL SELECT 2, 'b', 1
UNION ALL SELECT 3, 'c', 2
UNION ALL SELECT 4, 'd', 1
UNION ALL SELECT 5, 'f', null
UNION ALL SELECT 6, 'g', 5
UNION ALL SELECT 7, 'i', 5
UNION ALL SELECT 8, 'new', 7
UNION ALL SELECT 9, 'd', 4
GO
--查询处理
DECLARE @id int
SET @id = 1 -- 设置要查询的id
;WITH qry
AS(
SELECT * FROM menu WHERE id = @id
UNION ALL
SELECT A.* FROM menu A, qry B
WHERE A.fatherid = B.id
)
SELECT * FROM qry
GO
-- 删除测试
DROP TABLE menu
-- 结果
id name fatherid
----------- ---------- -----------
1 a NULL
2 b 1
4 d 1
9 d 4
3 c 2
-- 测试数据
CREATE TABLE menu(id int, name varchar(10), fatherid int)
INSERT menu
SELECT 1, 'a', null
UNION ALL SELECT 2, 'b', 1
UNION ALL SELECT 3, 'c', 2
UNION ALL SELECT 4, 'd', 1
UNION ALL SELECT 5, 'f', null
UNION ALL SELECT 6, 'g', 5
UNION ALL SELECT 7, 'i', 5
UNION ALL SELECT 8, 'new', 7
UNION ALL SELECT 9, 'd', 4
GO
-- 创建查询处理函数
CREATE FUNCTION dbo.f_cid(@id int)
RETURNS @r TABLE(id int, level int)
AS
BEGIN
DECLARE @l int
SET @l = 0
INSERT @r SELECT id, @l
FROM menu
WHERE id = @id
WHILE @@ROWCOUNT > 0
BEGIN
SET @l = @l + 1
INSERT @r SELECT A.id, @l
FROM menu A, @r B
WHERE A.fatherid = B.id
AND B.level = @l - 1
END
RETURN
END
GO
--调用函数实现查询
SELECT A.*
FROM menu A, dbo.f_cid(1) B
WHERE A.id = B.id
GO
-- 删除测试
DROP TABLE menu
DROP FUNCTION f_cid
-- 结果
id name fatherid
----------- ---------- -----------
1 a NULL
2 b 1
3 c 2
4 d 1
9 d 4