MySQL最大的价值所在? [英] MySQL greatest value in row?

问题描述

我正在将MySQL与PHP结合使用.就像我的表一样:(我使用的是3个值，但还有更多)

I'm using MySQL with PHP. This is like my table: (I'm using 3 values, but there are more)

id | 1 | 2 | 3
---+---+---+----
1  | 3 |12 |-29
2  | 5 |8  |8
3  | 99|7  |NULL

我需要在某一行中获取最大值的列名.它应该得到:

I need to get the greatest value's column name in a certain row. It should get:

id | maxcol
---+-------
1  |   2
2  |   2
3  |   1

是否有任何查询可以做到这一点?我一直在尝试，但是无法正常工作.

Are there any queries that will do this? I've been trying, but I can't get it to work right.

推荐答案

您是否正在寻找类似

Are you looking for something like the GREATEST function? For example:

SELECT id, GREATEST(col1, col2, col3)
    FROM tbl
    WHERE ...

使用 CASE 组合获取列名称的语句:

Combine it with a CASE statement to get column names:

SELECT id, CASE GREATEST(COALESCE(`1`, -2147483646), COALESCE(`2`, -2147483646), COALESCE(`3`, -2147483646))
         WHEN `1` THEN 1
         WHEN `2` THEN 2
         WHEN `3` THEN 3
         ELSE 0
      END AS maxcol
    FROM tbl
    WHERE ...

这不漂亮.您最好遵循Bill Karwin的建议并进行规范化，或者只是在PHP中进行处理.

It's not pretty. You'd do better to follow Bill Karwin's suggestion and normalize, or simply take care of this in PHP.

function findcol($cmp, $arr, $cols=Null) {
   if (is_null($cols)) {
      $cols = array_keys($arr);
   }
   $name = array_shift($cols);
   foreach ($cols as $col) {
       if (call_user_func($cmp, $arr[$name], $arr[$col])) {
           $name = $col;
       }
   }
   return $name;
}

function maxcol($arr, $cols=Null) {
   return findcol(create_function('$a, $b', 'return $a < $b;'), $arr, $cols);
}

这篇关于MySQL最大的价值所在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！