获取mysql条目的列

Is there a possibility to get the name of the column a database entry belongs to?

是否有可能获得数据库条目所属的列的名称?

Perhaps I have three columns with column names col1, col2 and col3. Now I want to select for every column the column with the maximum entry, something like this.

也许我有三个列，列名称为col1、col2和col3。现在我要选择每一列的列和最大的项，像这样。

Select name_of_column(max(col1,col2,col3)).

选择name_of_column(max(col1、col2 col3))。

I know that I can ask for the name of the columns by its ordinal position in the information_schema.COLUMNS table but how do I get the ordinal position of a database entry within a table?

我知道我可以根据information_schema中列的序号位置询问列的名称。但如何在表中获取数据库条目的序号位置呢?

3 个解决方案

#1

If you want to implement it in a pure SQL, you can use CASE statement and an additional variable:

如果您想在纯SQL中实现它，可以使用CASE语句和一个附加变量:

 SELECT @m := GREATEST(col1, col2), 
 CASE @m 
      WHEN col1 THEN 'col1' 
      WHEN col2 THEN 'col2' 
 END 
 FROM my_table

#2

This will show you the columns from a table

这将显示来自表的列

SHOW COLUMNS FROM 'table';

You'll then have to iterate through the results in a scripting language.

然后必须使用脚本语言迭代结果。

#3

You could do this:

你可以这样做:

select
    case true
        when col1 > col2 and col1 > col3 then 'col1'
        when col2 > col1 and col2 > col3 then 'col2'
        when col3 > col1 and col3 > col2 then 'col3'
    end 
from
    mytable

But what if the maximum value appears in multiple columns?

但是，如果最大值出现在多个列中呢?

#1