MySQL：获取多列的 MAX 或 GREATEST，但包含 NULL 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9831851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: get MAX or GREATEST of several columns, but with NULL fields
提问by Ivan
I'm trying to select the max date in three different fields in each record (MySQL) So, in each row, I have date1, date2 and date3: date1 is always filled, date2 and date3 can be NULL or empty The GREATEST statement is simple and concise but has no effects on NULL fields, so this doesn't work well:
我试图在每条记录的三个不同字段中选择最大日期 (MySQL) 所以，在每一行中，我有 date1、date2 和 date3：date1 总是被填充，date2 和 date3 可以是 NULL 或空 GREATEST 语句是简单而简洁，但对 NULL 字段没有影响，所以这不能很好地工作：
SELECT id, GREATEST(date1, date2, date3) as datemax FROM mytable
I tried also more complex solutions like this:
我还尝试了更复杂的解决方案，如下所示：
SELECT
CASE
WHEN date1 >= date2 AND date1 >= date3 THEN date1
WHEN date2 >= date1 AND date2 >= date3 THEN date2
WHEN date3 >= date1 AND date3 >= date2 THEN date3
ELSE date1
END AS MostRecentDate
Same problem here: NULL values are a GREAT problem in returning the right records
同样的问题：NULL 值是返回正确记录的一个大问题
Please, have you got a solution? Thanks in advance....
请问你有解决办法吗？提前致谢....
回答by Matt Dodge
Use COALESCE
用 COALESCE
SELECT id,
GREATEST(date1,
COALESCE(date2, 0),
COALESCE(date3, 0)) as datemax
FROM mytable
Update: This answer previously used IFNULLwhich does work, but as Mike Chamberlain pointed out in the comments, COALESCEis actually the preferred method.
更新：以前使用的这个答案IFNULL确实有效，但正如 Mike Chamberlain 在评论中指出的那样，COALESCE实际上是首选方法。
回答by ypercube??
If date1can never be NULL, then the result should never be NULL, right? Then you could use this, if you want NULLdates be not counted in the calculations (or change the 1000-01-01to 9999-12-31, if you want Nulls to count as the "end of time"):
如果date1永远不可能NULL，那么结果应该永远不会NULL，对吧？然后你可以使用它，如果你希望NULL日期不被计算在计算中（或者更改1000-01-01为9999-12-31，如果你希望 Nulls 计为“时间结束”）：
GREATEST( date1
, COALESCE(date2, '1000-01-01')
, COALESCE(date3, '1000-01-01')
) AS datemax
回答by hkf
COALESCEyour date columns before you use them in GREATEST.
COALESCE在GREATEST.
The way you handle them will depend on how you want to deal with NULL.. either high or low?
你处理它们的方式将取决于你想如何处理NULL……高还是低？
回答by santiago arizti
buuut, if all dates happen to be null? you still want to have null as an output, right? then you need this
buuut，如果所有日期都为空？您仍然希望将 null 作为输出，对吗？那么你需要这个
select nullif(greatest(coalesce(<DATEA>, from_unixtime(0)), coalesce(<DATEB>, from_unixtime(0))), from_unixtime(0));
Now, if both are null you get null, if one of them is not null of both of them are not null, you get the greatest.
现在，如果两者都为空，则为空，如果其中之一不为空，则两者都不为空，则为最大。
This is crazy, especially if you will use it multiple times, for this then you might want to create it as a function, like this:
这太疯狂了，特别是如果您将多次使用它，那么您可能希望将其创建为一个函数，如下所示：
delimiter //
drop function if exists cp_greatest_date//
create function cp_greatest_date ( dateA timestamp, dateB timestamp ) returns timestamp
deterministic reads sql data
begin
# if both are null you get null, if one of them is not null of both of them are not null, you get the greatest
set @output = nullif(greatest(coalesce(dateA, from_unixtime(0)), coalesce(dateB, from_unixtime(0))), from_unixtime(0));
# santiago arizti
return @output;
end //
delimiter ;
Then you can use it like this
然后你可以像这样使用它
select cp_greatest_date(current_timestamp, null);
-- output is 2017-05-05 20:22:45
