MySQL返回最大值；如果一列没有值，则返回null [英] MySQL return max value or null if one column has no value

问题描述

我尝试获取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.

这篇关于MySQL返回最大值；如果一列没有值，则返回null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！