如何在SQL中找到不同列中的前三个最大值

Question

您好，我有一张包含J1，J2，J3，J4，J5，J6和J7列的表格。我想要从这些列中找到最大的三个值，并将它们命名为L1、L2和L3。

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

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

- Jobin Joseph

---

这个表中有主键吗？ - Giorgos Betsos

PL/SQL是Oracle的存储过程语言。SQL Server是完全不同的数据库管理系统。那么你真正使用的是什么？是Oracle还是SQL Server？从例子来看，应该是SQL Server而不是Oracle。 - user330315

通常这是数据模型出现问题的标志——所有相同“类型”的值（使得比较两个这样的值或在它们上计算聚合有意义）都应该在一个列中。如果数字1-7实际上是嵌入到您的列名中的有意义的数据，那么还应该有另外一列。 - Damien_The_Unbeliever

我尝试了Giorgos先生提供的查询，但没有提供所需的结果。对于这个查询有什么建议吗？ SELECT id, [1], [2], [3].....,[7], [8] INTO TEMP6 FROM ( SELECT id, Val, rn FROM (SELECT id, Val, Col, ROW_NUMBER() OVER (PARTITION BY id ORDER BY Val DESC ) AS rn FROM TEMP5 UNPIVOT ( Val FOR Col IN ([Week1], [Week2],[Week3],[Week4].....[Week53] )) AS unpvt) AS t WHERE t.rn <= 8) AS src PIVOT (MAX(Val) FOR rn IN ([1], [2],..., [7], [8])) AS pvt - Jobin Joseph

Answer 1

如果您的表有主键，比如id，那么您可以使用一个查询来使用 UNPIVOT：

SELECT *
FROM (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY Val) AS rn
  FROM JTable
  UNPIVOT (
     Val FOR Col IN (J1, J2, J3, J4, J5, J6, 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, Val, Col,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY Val) AS rn
    FROM JTable
    UNPIVOT (
       Val FOR Col IN (J1, J2, J3, J4, J5, J6, J7)) AS unpvt) AS t
  WHERE t.rn <= 3) AS src
PIVOT (
   MAX(Val) FOR rn IN ([1], [2], [3])) AS pvt

- Giorgos Betsos

---

它没有获取前3个值，我稍微修改了查询，因为我需要从53列的列表中获取前8个值，而且它有超过2000行。以下是我用于查询的代码：SELECT id, [1], [2], [3].....,[7], [8] INTO TEMP6 FROM ( SELECT id, Val, rn FROM (SELECT id, Val, Col, ROW_NUMBER() OVER (PARTITION BY id ORDER BY Val DESC ) AS rn FROM TEMP5 UNPIVOT ( Val FOR Col IN ([Week1], [Week2],[Week3],[Week4].....[Week53] )) AS unpvt) AS t WHERE t.rn <= 8) AS src PIVOT (MAX(Val) FOR rn IN ([1], [2],..., [7], [8])) AS pvt - Jobin Joseph