Поиск значений LEAST/GREATEST из комбинированных столбцов, игнорировать 0 и NULL- MYSQL

У меня есть набор данных с кучей строк для ежемесячных выплат заработной платы для каждого аккаунта. у нас есть 6 столбцов для этого -

Зарплата_1, Зарплата_2, Зарплата_3, Зарплата_4, Зарплата_5 и Зарплата_6.

Иногда зарплаты 3, 4, 5, 6 и иногда 2 не заселены, иногда ни одна не заселена, потому что они безработные. В этом случае у нас есть 0 в поле.

Что мне нужно сделать, это объединить все зарплаты и найти максимальные и минимальные из этих столбцов ---

   Select

        Greatest(Salary_1, Salary_2, Salary_3, Salary_4, Salary_5, Salary_6) as MaxSal, 
        Least(COALESCE(Salary_1, Salary_2, Salary_3, Salary_4, Salary_5, Salary_6),0) as MinSal

from

    (select
     sal1 as Salary_1, Select sal2 as Salary_2, Sal3 as Salary_3, sal4 as Salary_4, Sal5 as Salary_5, Sal6 as Salary_6
     from ....)a

Проблема в том, что это возвращает правильное значение для Max Sal, но 0,00 для Min, потому что это минимальное значение, но оно не позволяет мне игнорировать 0s, но в этом случае 0 не является минимальным значением зарплаты, которое я хочу, мне нужно второе самое низкое значение здесь.

Я попытался установить исходные значения Sal1-Sal6 в NULLIF 0, и он возвращает NULL для макс. И 0 для мин.

На что еще я могу взглянуть? COALESCE в сочетании с NULLIF не работает для меня, что было рекомендовано по предыдущим вопросам. Спасибо!

Источник

2 ответа

Решение

Greatest и Least не игнорируют нули, как функции агрегации; вам нужно что-то сделать, чтобы избежать их. Одним из вариантов является что-то вроде этого:

Greatest(IFNULL(Salary_1 ,0), ...)

Least(
     CASE WHEN Salary_1 IS NULL OR Salary_1 = 0 THEN /*some huge value*/ ELSE Salary_1 END
   , CASE WHEN Salary_2
   ....)
Источник

Это может быть проще всего развернуть и агрегировать данные:

select id, max(salary), min(salary)
from ((select id, salary_1 as salary from t) union all
      (select id, salary_2 as salary from t) union all
      . . .
     ) t
group by id;

Это определенно дороже, чем гигант case выражение. С другой стороны, он менее подвержен ошибкам.

Реальное предложение - исправить вашу модель данных. Попытка сохранить массив в нескольких столбцах, как правило, является признаком плохой модели данных. Более подходящий метод будет иметь одну строку на salary вместо того, чтобы поместить их в отдельные столбцы.

Источник
Другие вопросы по тегам