mysql - 插入具有最高值的列的列名

Question

所以我在 MySQL 中有下表:

user_id  column1  column2  column3  column4  column5
-------  -------  -------  -------  -------  -------
1        1        15       12       20       25
2        5        9        6        15       10
3        7        12       21       9        17

我想将每条记录的 user_id 和具有最高值的列名插入到新表中，如下所示:

user_id  highest_val
-------  -----------
1        column5
2        column4
3        column3 

谁能帮帮我？谢谢!

到目前为止，我已经设法只使用以下查询获取列的值，而不是列名本身:

insert into table2 (user_id, highest_val) SELECT user_id, GREATEST(column1, column2, column3, column4, column5) FROM table1

Answer 1

假设没有值是NULL，你可以使用case表达式:

select t.*,
       (case when column1 = greatest(column1, column2, column3, column4, column5) then 'column1'
             when column2 = greatest(column1, column2, column3, column4, column5) then 'column2'
             when column3 = greatest(column1, column2, column3, column4, column5) then 'column3'
             when column4 = greatest(column1, column2, column3, column4, column5) then 'column4'
             when column5 = greatest(column1, column2, column3, column4, column5) then 'column5'
      end) as greatest_column             
from t;

你也可以这样表述:

select t.*,
       (case greatest(column1, column2, column3, column4, column5)
             when column1 then 'column1'
             when column2 then 'column2'
             when column3 then 'column3'
             when column4 then 'column4'
             when column5 then 'column5'
      end) as greatest_column             
from t;