SQL:确定哪一列在每一行中具有最大值

Question

我有一个包含 ID、A、B、C、D、E 列的表(总共 10 个数字列)

对于每一行，我需要找到哪一列具有最大值，以及该值是什么。

例如。这是我的表的 1 行:ID A B C D E F G H I JXY 5 4 9 5 0 1 3 2 1 7
我想生成 2 个新列:
最大值，等于 9，和
maxcol，等于“C”

除了大量的 IF 语句之外，还有什么建议吗？

Answer 1

我手头没有 sql 处理器，但有一些类似的东西

select id , colName
from 
(select id, 'A' as colName, a as value union all
select id, 'B' as colName, b as value union all
select id, 'C' as colName, c as value union all
select id, 'D' as colName, d as value union all
select id, 'E' as colName, e as value union all
select id, 'F' as colName, f as value union all
select id, 'G' as colName, g as value union all
select id, 'H' as colName, h as value union all
select id, 'I' as colName, i as value union all
select id, 'J' as colName, j as value)
group by id having max(value)