--> 测试数据:#P_Class
IF OBJECT_ID('TEMPDB.DBO.#P_Class') IS NOT NULL DROP TABLE #P_Class
GO
CREATE TABLE #P_Class([id] INT,[name] VARCHAR(4),[fatherId] INT)
INSERT #P_Class
SELECT 1,'A',0 UNION ALL
SELECT 2,'B',0 UNION ALL
SELECT 3,'A1',1 UNION ALL
SELECT 4,'A1_1',3 UNION ALL
SELECT 5,'B1',2 UNION ALL
SELECT 6,'B1_1',5
--> 测试数据:#P_Product
IF OBJECT_ID('TEMPDB.DBO.#P_Product') IS NOT NULL DROP TABLE #P_Product
GO
CREATE TABLE #P_Product([id] INT,[name] VARCHAR(2),[classId] INT,[sort] INT)
INSERT #P_Product
SELECT 1,'n1',4,1 UNION ALL
SELECT 2,'n2',4,2 UNION ALL
SELECT 3,'n3',4,3 UNION ALL
SELECT 4,'n4',4,4 UNION ALL
SELECT 5,'n5',4,5 UNION ALL
SELECT 6,'n6',4,6 UNION ALL
SELECT 7,'n7',4,7 UNION ALL
SELECT 8,'p1',6,1 UNION ALL
SELECT 9,'p2',6,2 UNION ALL
SELECT 10,'p3',6,3 UNION ALL
SELECT 11,'p4',6,4 UNION ALL
SELECT 12,'p5',6,5 UNION ALL
SELECT 13,'p6',6,6
--------------开始查询--------------------------
;WITH cte AS
(
SELECT *,[name] AS [father_name],[level]=1 FROM #P_Class WHERE [fatherId]=0
UNION ALL
SELECT a.*,t.[father_name] AS [father_name],[level]=[level]+1 FROM #P_Class AS a,cte AS t WHERE a.[fatherId]=t.[id]
)
SELECT p.* FROM cte AS c
JOIN #P_Product AS p
ON c.[id]=p.[classId]
WHERE p.[sort]<=5
AND NOT EXISTS(SELECT 1 FROM cte WHERE [father_name]=c.[father_name] AND [level]>c.[level])
----------------结果----------------------------
/*
id name classId sort
1 n1 4 1
2 n2 4 2
3 n3 4 3
4 n4 4 4
5 n5 4 5
8 p1 6 1
9 p2 6 2
10 p3 6 3
11 p4 6 4
12 p5 6 5
*/