Как найти наименьший ненулевой столбец в одной конкретной строке в SQL?

Я пытаюсь найти самое низкое число в двух столбцах строки в той же таблице, с оговоркой, что один из столбцов может быть нулевым в определенной строке. Если один из столбцов имеет значение null, я хочу, чтобы значение в другом столбце возвращалось для этой строки, так как в этом случае это самый низкий столбец, отличный от нуля. Если я использую функцию minimum() в MySQL 5.1:

select least(1,null)

Это возвращает null, что не то, что я хочу. Мне нужен запрос для возврата 1 в этом случае.

Мне удалось получить результат, который я хочу получить с помощью этого запроса:

select least(coalesce(col1, col2)) , coalesce(col2,col1))

Пока col1 и col2 оба не равны нулю, каждый оператор coalesce возвращает число, а наименьшее() обрабатывает поиск самого низкого.

Есть ли более простой/быстрый способ сделать это? Я использую MySQL в этом случае, но общие решения приветствуются.

Ответ 1

К сожалению (для вашего случая) поведение LEAST было изменено в MySQL 5.0.13 (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_least) - оно использовало для возврата только NULL если все аргументы равны NULL.

Это изменение было даже указано как ошибка: http://bugs.mysql.com/bug.php?id=15610 Но исправление было только для документации MySQL, объясняя новое поведение и разрыв совместимости.

Ваше решение было одним из рекомендуемых обходных решений. Другой может использовать оператор IF:

SELECT IF(Col1 IS NULL OR Col2 IS NULL, COALESCE(Col1, Col2), LEAST(Col1,Col2))

Ответ 2

Это может немного улучшить (возможно, придется преобразовать в соответствующий синтаксис MySql):

SELECT
  CASE
    WHEN Col1 IS NULL THEN Col2
    WHEN Col2 IS NULL THEN Col1
    ELSE Least(Col1, Col2)
  END

Другая альтернатива (возможно, медленнее, но стоит попробовать):

SELECT Col1
WHERE Col2 IS NULL
UNION
SELECT Col2
WHERE Col1 IS NULL
UNION
SELECT least(Col1, Col2)
WHERE Col1 IS NOT NULL AND Col2 IS NOT NULL

Ответ 3

В зависимости от ситуации вашего угла с учетом всех значений null, я бы пошел на такой синтаксис, который более читабельен (более простое решение, если у вас ровно два столбца ниже!)

SELECT LEAST( IFNULL(5, ~0 >> 1), IFNULL(10, ~0 >> 1) ) AS least_date;
-- Returns: 5

SELECT LEAST( IFNULL(null, ~0 >> 1), IFNULL(10, ~0 >> 1) ) AS least_date;
-- Returns: 10

SELECT LEAST( IFNULL(5, ~0 >> 1), IFNULL(null, ~0 >> 1) ) AS least_date;
-- Returns: 5

SELECT LEAST( IFNULL(null, ~0 >> 1), IFNULL(null, ~0 >> 1)) AS least_date
-- Returns: @MAX_VALUE (If you need to use it as default value)

SET @MAX_VALUE=~0 >> 1;
SELECT LEAST( IFNULL(null, @MAX_VALUE), IFNULL(null, @MAX_VALUE)) AS least_date;
-- Returns: @MAX_VALUE (If you need to use it as default value). Variables just makes it more readable!

SET @MAX_VALUE=~0 >> 1;
SELECT NULLIF(
    LEAST( IFNULL(null, @MAX_VALUE), IFNULL(null,@MAX_VALUE)),
    @MAX_VALUE
) AS least_date;
-- Returns: NULL

Это мой предпочтительный способ, если

вы можете убедиться, что хотя бы один столбец не может быть null
в ситуации с угловым случаем (все столбцы null) вы хотите иметь ненулевое значение по умолчанию, которое больше любого возможного значения или может быть ограничено определенным пороговым значением
Вы можете иметь дело с переменными, чтобы сделать этот оператор еще более удобочитаемым.

Если вы зададите себе вопрос, что означает ~0 >> 1: Это всего лишь короткая рука для того, чтобы сказать "Дайте мне наибольшее количество доступных". См. Также: fooobar.com/questions/65809/...

Еще лучше, если у вас есть только два столбца, вы можете использовать:

SELECT LEAST( IFNULL(@column1, @column2), IFNULL(@column2, @column1) ) AS least_date;
-- Returns: NULL (if both columns are null) or the least value

Ответ 4

Почему бы не установить значение одного столбца равным другому столбцу, когда оно равно NULL?

SELECT LEAST(IFNULL(COL1, COL2), IFNULL(COL2, COL1));

с кодом выше, нулевое значение будет проигнорировано, если оба значения равны нулю.

например.

COL1 = NULL, COL2 = 5

LEAST(IFNULL(NULL, 5), IFNULL(5, NULL)) -> LEAST(5, 5) -> 5

COL1 = 3, COL2 = NULL

LEAST(IFNULL(3, NULL), IFNULL(NULL, 3)) -> LEAST(3, 3) -> 3

COL1 = NULL, COL2 = NULL

LEAST(IFNULL(NULL, NULL), IFNULL(NULL, NULL)) -> LEAST(NULL, NULL) -> NULL