嗨我有一个包含J1,J2,J3,J4,J5,J6,J7列的表格.我想从这些列中找到最大的3个值为L1,L2,L3.
我尝试了下面的查询来找到第一个最大的
SELECT (
SELECT Max(v) FROM (
VALUES
([J1]),([J2]),([J3]),([J4]),([J5]),([J6]),([J7])
) AS value(v)
) as [L1]FROM dbo.JTable
解决方法
如果您的表有PK,比如说id,那么您可以使用员工
UNPIVOT的查询：
SELECT *
FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY Val) AS rn
FROM JTable
UNPIVOT (
Val FOR Col IN (J1,J7)) AS unpvt) AS t
WHERE t.rn <= 3
如果你想要每个id一行,那么你可以使用PIVOT撤消UNPIVOT操作：
SELECT id,[1],[2],[3]
FROM (
SELECT id,Val,rn
FROM (
SELECT id,Col,ROW_NUMBER() OVER (PARTITION BY id ORDER BY Val) AS rn
FROM JTable
UNPIVOT (
Val FOR Col IN (J1,J7)) AS unpvt) AS t
WHERE t.rn <= 3) AS src
PIVOT (
MAX(Val) FOR rn IN ([1],[3])) AS pvt
