如何从SQL中的不同列中查找第一,第二和第三大值

我尝试了下面的查询来找到第一个最大的

SELECT (
    SELECT Max(v) FROM (
        VALUES 
          ([J1]),([J2]),([J3]),([J4]),([J5]),([J6]),([J7])
    ) AS value(v)
) as [L1]FROM dbo.JTable

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!