sql - 连续 4 列中的最大值

Question

我有包含以下字段的 test_scores 表:

表架构:

  id (number)
  score1  (number)
  score2  (number)
  score3  (number)
  score4  (number)

示例数据:

id score1 score2 score3 score4
1  10     05      30    50
2  05     15      10    00
3  25     10      05    15

预期结果集:

id col_name col_value
1  score4   50
2  score2   15
3  score1   25

什么是好的 SQL？(我正在使用 MySQL。)

原始要求不包括行集中的列名称。我能够使用以下 SQL 获得结果:

SELECT   A.id, MAX(A.score) AS max_score
FROM     (
            SELECT id, score1 as score FROM test_scores UNION
            SELECT id, score2 as score FROM test_scores UNION
            SELECT id, score3 as score FROM test_scores UNION
            SELECT id, score4 as score FROM test_scores
          ) AS A
GROUP BY A.id

但是随着在结果集中引入 col_name 我感到很困惑..

Answer 1

我使用以下 SQL 解决了这个问题:

SELECT id, GREATEST(score1, score3, score3, score4) AS col_value,
       CASE GREATEST(score1, score3, score3, score4) 
         WHEN score1 THEN 'score1'
         WHEN score2 THEN 'score2'
         WHEN score3 THEN 'score3'
         WHEN score4 THEN 'score4'
       END AS col_name
FROM test_scores

Please let me know if there is a better solution.