如何让MySQL MAX（）认为NULL是最小的可能值？(How to make MySQL MAX() consider NULL the smallest possible value?)

如何让MySQL MAX（）认为NULL是最小的可能值？(How to make MySQL MAX() consider NULL the smallest possible value?)

我有一个看起来有点像这样的查询：

SELECT weekEnd, MAX(timeMonday) FROM timesheet GROUP BY weekEnd

timeMonday的有效值为：null，-1,0,1-24。 目前，MAX（）将这些值的优先级置于null，-1,0,1-24的顺序中，但实际上我想要的是-1，null，0,1-24，因此null被认为更高比-1。 我知道MAX无法做到这一点，那么实现它的最简单方法是什么？

I have a query that looks a bit like this:

SELECT weekEnd, MAX(timeMonday) FROM timesheet GROUP BY weekEnd

The valid values for timeMonday are: null, -1, 0, 1-24. At the moment, MAX() places the preference of those values in the order null, -1, 0, 1-24, however what I actually want is -1, null, 0, 1-24, so that null is considered higher than -1. I know MAX cant do this, so what's the easiest way to achieve it?

最满意答案

SELECT weekEnd，MAX（coalesce（timeMonday， - 。5））FROM timesheet GROUP BY weekEnd

然后将-.5转换回null

SELECT weekEnd, MAX(coalesce(timeMonday,-.5)) FROM timesheet GROUP BY weekEnd

Then convert -.5 back to null