110,499
社区成员
发帖
与我相关
我的任务
分享
select a.name,b.name from (select bigclassid,name from test where upgradeid=0) a inner join (select * from test where upgradeid>0) b on a.bigclassid =b.upgradeid order by b.upgradeid
把test换成你要的表名即可
;WITH c_distinct AS
( SELECT * FROM @t WHERE UpGradeID = 0)
SELECT ParentName = a.Name, ChildName = t2.Name
FROM( SELECT * FROM c_distinct) a
CROSS APPLY( SELECT t.Name FROM @t t WHERE t.UpGradeID = a.BigClassID) t2
把这段中@t变量换成你的表名应该就是了
DECLARE @t TABLE
(
BigClassID int,
Name varchar(10),
UpGradeID int
);
INSERT INTO @t
SELECT 1,'食品干货', 0 UNION ALL
SELECT 2,'日用', 0 UNION ALL
SELECT 3,'休闲食品', 1 UNION ALL
SELECT 4,'饮料', 1 UNION ALL
SELECT 5,'烟', 1 UNION ALL
SELECT 6,'酒水', 1 UNION ALL
SELECT 7,'健康美容', 2 UNION ALL
SELECT 8,'纸制用品', 2 UNION ALL
SELECT 9,'饼干', 1 UNION ALL
SELECT 10,'清洁用品', 2
--准备数据
;WITH c_distinct AS
(
SELECT *
FROM @t
WHERE UpGradeID = 0
)
SELECT ParentName = a.Name,
ChildName = t2.Name
FROM
(
SELECT *
FROM c_distinct) a
CROSS APPLY
(
SELECT t.Name
FROM @t t
WHERE t.UpGradeID = a.BigClassID) t2
测试结果:
ParentName ChildName
---------- ----------
食品干货 休闲食品
食品干货 饮料
食品干货 烟
食品干货 酒水
食品干货 饼干
日用 健康美容
日用 纸制用品
日用 清洁用品
(8 row(s) affected)
你是否想要递归查询,类似这样?