关于#SQL#的迭代、父子结构查询问题，如何解决？

Question

我用的SQLSERVER数据库，有一个商品分类表，一个商品表。 商品分类表中有上下级关系，用parentid来连接。商品表中一个商品只有一个分类ID，现在想查询所有商品的分类及分类上上级、上上级 。想加个中间表来保存商品和分类的关系，以便于查询。C#用的EF。

数据表参考

商品表

ID	CategoryID	商品名称
1	100101	商品1
2	2001	商品2

分类表

ID	ParentID	分类名称
10	NULL	分类1
1001	10	分类11
100101	1001	分类111
20	NULL	分类2
2001	20	分类22

想要查询的结果

商品ID	分类ID
1	10
1	1001
1	100101
2	20
2	2001

期望能给我sql语句，不用程序来实现。数据量比较大，sql语句批量执行时间比较快。谢谢

Answer 1

DECLARE @商品表 TABLE(ID INT NOT NULL,    CategoryID INT NOT NULL,    商品名称 NVARCHAR(1000))
INSERT @商品表(ID, CategoryID, 商品名称)VALUES(1,100101,'商品1'),(2,2001,'商品2')
DECLARE @分类表 TABLE(ID INT NOT NULL,    ParentID INT NULL,    分类名称 NVARCHAR(1000))
INSERT @分类表(ID,ParentID,分类名称) VALUES
(10, NULL, '分类1'), (1001, 10, '分类11'), (100101, 1001, '分类111'), (20, NULL, '分类2'), (2001, 20, '分类22')

;WITH list AS(
SELECT a.ID, a.CategoryID,b.ParentID FROM @商品表 a INNER JOIN @分类表 b ON a.CategoryID = b.ID
UNION ALL SELECT b.ID, a.ID,a.ParentID FROM @分类表 a INNER JOIN list b ON a.ID=b.ParentID)
SELECT list.ID 商品ID, list.CategoryID 分类ID FROM list ORDER BY list.ID,list.CategoryID