MySQL返回最大值；如果一列没有值，则返回null

本文介绍了MySQL返回最大值；如果一列没有值，则返回null的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我尝试获取mysql select的最大值，但如果有一行不包含时间戳，则希望将其设置为null/empty/0.

I try to get the max value of a mysql select, but want to have it null/empty/0 if there is one row containing no timestamp.

表格统计信息(简化):

Table stats (simplyfied):

ID CLIENT ORDER_DATE CANCEL_DATE 1 5 1213567200 2 5 1213567200 3 6 1210629600 1281736799 4 6 1210629600 1281736799 5 7 1201042800 1248386399 6 7 1201042800 7 8 1205449200 1271282399

我现在希望获得最低的订购日期(没问题，因为它永远不会为空)，并且 最大取消日期.如果客户已经取消了订阅，则填写取消日期，但是如果他仍处于活动状态，则根本没有取消日期.

I'm now looking to get the lowest order date (no problem, as it is never empty), and the maximum cancel date. If the client has already cancelled his subscription, the cancel date is filled, but if he is still active, there is no cancel date at all.

查询:

SELECT ID, min(ORDER_DATE) AS OD, max(CANCEL_DATE) AS CD FROM stats GROUP BY CLIENT

返回:

ID OD CD 5 1213567200 // fine 6 1210629600 1281736799 // fine 7 1201042800 1248386399 // Should be empty 8 1205449200 1271282399 // fine

如果一个客户端有一个(或多个)空列，我不知道如何返回empty/0/NULL.还尝试使用NULL字段.

I can't figure it out how to return empty/0/NULL if there is one (or more) empty colums for a client. Also tried with NULL fields.

感谢任何提示.

推荐答案

我不知道它将有多快，但是我想可以这样解决:

I don't know how fast it will be but I guess it can be solved like this:

SELECT ID, min(ORDER_DATE) AS OD, IF(COUNT(*)=COUNT(CANCEL_DATE),max(CANCEL_DATE),NULL) AS CD FROM stats GROUP BY CLIENT

我无法对其进行测试，但是此解决方案背后的想法是count(cancel_date)应该计算所有非空值条目，并且如果它等于count(*)，则意味着没有空值，并且它将返回max(cancel_date) ，否则为null.

I couldn't test it but the idea behind this solution is that count(cancel_date) should count all not null value entries and if it's equal to count(*) that means that there are no null values and it will return max(cancel_date), otherwise null.