mysql - SQL:至少从一行的一组列中获取第三大值

Question

我需要一个 SQL 查询来从一行的一组列中获取第二大值。例如，如果这些是我的表的行:

id | col1 | col2 | col3 | col4 | coln |
1  |   5  |   7  |   9  |  3   |  10  |
2  |   13 |   14 |   2  |  54  |  11  |

对于 rowid 1 - 我需要值 9， rowid 2 - 我需要值 14

Answer 1

恐怕，如果没有通用的表表达式和/或窗口函数，并且不求助于编写过程，这在 MySQL 中会变得非常冗长

SELECT t.id, t.val second_largest
-- unpivot your columns into a table
FROM (
  SELECT id, col1 val FROM my_table UNION ALL
  SELECT id, col2     FROM my_table UNION ALL
  SELECT id, col3     FROM my_table UNION ALL
  SELECT id, col4     FROM my_table UNION ALL
  SELECT id, coln     FROM my_table
) t

-- retain only those records, where there exists exactly one record with a
-- column value greater than any other column value with the same id
WHERE 1 = (
  SELECT COUNT(*) 
  -- Here, use unions to be sure that every value appears exactly once
  FROM (
    SELECT id, col1 val FROM my_table UNION
    SELECT id, col2     FROM my_table UNION
    SELECT id, col3     FROM my_table UNION
    SELECT id, col4     FROM my_table UNION
    SELECT id, coln     FROM my_table
  ) u
  WHERE t.id = u.id
  AND t.val < u.val
)

这是 SQLFiddle检查它(感谢 bluefeet 对模式的提醒!)。上述解决方案将在每一行中找到第二大列值，即使最大列值出现不止一次。