MySQL最大的价值所在? [英] MySQL greatest value in row?
本文介绍了MySQL最大的价值所在?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在将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屋!
查看全文