MySQL: получите MAX или GREATEST из нескольких столбцов, но с полями NULL

Я пытаюсь выбрать максимальную дату в трех разных полях в каждой записи (MySQL) Итак, в каждой строке у меня есть date1, date2 и date3: date1 всегда заполняется, date2 и date3 могут быть NULL или пустым Инструкция GREATEST проста и краткая, но не влияет на поля NULL, поэтому это не работает:

SELECT id, GREATEST(date1, date2, date3) as datemax FROM mytable

Я попробовал и более сложные решения вроде этого:

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

Такая же проблема: значения NULL - БОЛЬШАЯ проблема при возврате правильных записей

Пожалуйста, у вас есть решение? Спасибо заранее.

Ответ 1

Используйте COALESCE

SELECT id, 
   GREATEST(date1, 
     COALESCE(date2, 0),
     COALESCE(date3, 0)) as datemax 
FROM mytable

Обновление: этот ответ ранее использовал IFNULL, который работает, но, как отметил Майк Чемберлен в комментариях, COALESCE - фактически предпочтительный метод.

Ответ 2

Если 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

Ответ 3

COALESCE столбцы даты, прежде чем использовать их в GREATEST.

То, как вы их обрабатываете, будет зависеть от того, как вы хотите иметь дело с NULL.. либо высоким, либо низким?

Ответ 4

buuut, если все даты имеют значение null? вы все еще хотите иметь нуль в качестве вывода, не так ли? то вам это нужно

select nullif(greatest(coalesce(<DATEA>, from_unixtime(0)), coalesce(<DATEB>, from_unixtime(0))), from_unixtime(0));

Теперь, если оба значения равны null, вы получаете null, если один из них не является нулевым из обоих из них, не является нулевым, вы получаете наибольшее значение.

Это безумие, особенно если вы будете использовать его несколько раз, для этого вам может понадобиться создать его как функцию, например:

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 ;

Тогда вы можете использовать его так:

select cp_greatest_date(current_timestamp, null);
-- output is 2017-05-05 20:22:45