34,590
社区成员
发帖
与我相关
我的任务
分享
--是这样吗?
;WITH 产品类别 (ID, NAME) AS
(
SELECT 1, 'A' UNION ALL
SELECT 2, 'B'
),
产品 (ID, NAME, FK_ID) AS
(
SELECT 1, 'NAME_1', 1 UNION ALL
SELECT 2, 'NAME_2', 2 UNION ALL
SELECT 3, 'NAME_3', 1
)
SELECT
B.ID AS 产品类别ID,
B.NAME AS 产品类别NAME,
A.ID AS 产品ID,
A.NAME AS 产品NAME
FROM
(
SELECT * FROM
(
SELECT RANK() OVER(PARTITION BY FK_ID ORDER BY ID) AS PLACE, *
FROM 产品
) AS C
WHERE C.PLACE =1
)AS A
INNER JOIN 产品类别 AS B ON A.FK_ID=B.ID
ORDER BY 产品类别ID
SELECT c.名称, p.名称
FROM 类别表 c
OUTER APPLY (SELECT TOP 1 *
FROM 产品表
WHERE 产品表.类别ID = c.ID
) p