22,209
社区成员
发帖
与我相关
我的任务
分享
--2005
;WITH LiangLoveLan AS
(
SELECT rowid=ROW_NUMBER() OVER(PARTITION BY 分组 ORDER BY 价格),*
FROM tb
)
SELECT
分组,
ISNULL([1],0) AS 最低价一,
ISNULL([2],0) AS 最低价二,
ISNULL([3],0) AS 最低价三
FROM (
SELECT rowid,分组,价格 FROM LiangLoveLan WHERE rowid <= 3
) AS A
PIVOT(MAX(价格) FOR rowid IN([1],[2],[3])) AS pvt
SELECT
分组,
MAX(CASE WHEN rowid=1 THEN 价格 ELSE 0 END) AS 最低价一,
MAX(CASE WHEN rowid=2 THEN 价格 ELSE 0 END) AS 最低价二,
MAX(CASE WHEN rowid=3 THEN 价格 ELSE 0 END) AS 最低价三
FROM (
SELECT rowid=(SELECT COUNT(*)+1 FROM tb
WHERE 分组= A.分组 AND 价格 < A.价格),*
FROM tb AS A
) AS T
WHERE rowid <= 3
GROUP BY 分组;