Блог

Главная - Вопросы по программированию - Могу ли я СГРУППИРОВАТЬ BY в MySQL, но при этом игнорировать нулевые значения при группировании, отбрасывая результирующую строку, если это необходимо?

Могу ли я СГРУППИРОВАТЬ BY в MySQL, но при этом игнорировать нулевые значения при группировании, отбрасывая результирующую строку, если это необходимо?

#mysql #group-by #left-join

#mysql #группировка по #левое соединение

Вопрос:

Мои таблицы:

t1

 col_a  col_b
1       100    
1       200
1       300
2       400

t2

 col_a  col_b
100      5
100      6

t3

 col_a  col_b
5       100
6       200
6       300

Если я выполняю запрос и оставляю 3 таблицы в порядке, я получаю:

 1   100  5     100
1   100  6     200
1   100  6     300
1   200  null  null
1   300  null  null
2   400  null  null

Если я добавлю группу по t1.col_a, t2.col_b:

 1   100           5      100
1   100           6      (200 or 300)
1   (200 or 300)  null   null
2   400           null   null

Но я не хочу, чтобы отображалась 3-я строка, потому что она не имеет значения в t2.col_b. Я мог бы добавить условие, при котором этот столбец не равен null, но это удалило бы последнюю строку, которая должна остаться.

В идеальном запросе я хотел бы видеть:

 1   100           5      100
1   100           6      (200 or 300)
2   400           null   null

Комментарии:

1. Почему вам нужна не 3-я строка, а последняя строка. Обе строки не имеют отношения к t2. Невозможно удалить одну строку и получить другую, если у вас нет правила.

2. в основном первая таблица представляет пользователя, вторая — свойство пользователя, третья — свойство свойства. я хочу, чтобы все пользователи показывали, есть ли у них какие-либо назначенные свойства — вот почему последняя строка должна остаться. я не хочу, чтобы эта третья строка отображалась, потому что у меня уже есть две строки для этого пользователя. я понимаю, что во втором столбце результатов есть уникальная информация, но я действительно забочусь только о дублировании пользовательских строк, когда третья таблица уникальна.

Ответ №1:

Попробуйте это. t1.col_b немного сложнее, потому что, если существует несколько значений t1.col_a без ссылки на t2, этот запрос выбирает случайное значение для этого.

 SELECT
    sub.col_a,
    IFNULL(sub.relation,t1.col_b),
    t2.col_b,
    t3.col_b
FROM(
    SELECT 
        t1.col_a,
        group_concat(DISTINCT t2.col_a) AS relation 
    FROM table1 AS t1
    LEFT JOIN table2 AS t2 ON t2.col_a = t1.col_b
    GROUP BY t1.col_a
) AS sub
LEFT JOIN table1 AS t1
    ON t1.col_a = sub.col_a
    AND (t1.col_b IN (sub.relation) OR sub.relation IS NULL)
LEFT JOIN table2 AS t2
    ON t2.col_a = t1.col_b
LEFT JOIN table3 AS t3
    ON t3.col_a = t2.col_b
GROUP BY t1.col_a, t2.col_b

Ответ №2:

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

 SELECT * FROM t1
 JOIN t2 ON t1.col_b = t2.col_a
 LEFT JOIN t3 ON t2.col_b = t3.col_a

UNION

SELECT * FROM t1
  LEFT JOIN t2 ON t1.col_b = t2.col_a
  LEFT JOIN t3 ON t2.col_b = t3.col_a
GROUP BY t1.col_a

Обратите внимание, что в первом запросе нет ЛЕВОГО СОЕДИНЕНИЯ. Это потому, что мы не хотим никаких нулевых результатов в этом запросе. Этот запрос дает вам:

 t1.col_a    t2.col_a    t3.col_a    t3.col_b
   1           100          5         100
   1           100          6         200
   1           100          6         300

И второй запрос дает вам:

 t1.col_a    t2.col_a    t3.col_a    t3.col_b
   1           100          5         100
   2           400         NULL       NULL

Наконец, когда вы выполняете ОБЪЕДИНЕНИЕ, оно избавляется от повторяющегося результата (первая строка в первом результирующем наборе равна первой строке во втором результирующем наборе). Это поведение команды ОБЪЕДИНЕНИЯ по умолчанию, приводящее к:

 t1.col_a    t2.col_a    t3.col_a    t3.col_b
   1           100          5         100
   1           100          6         200
   1           100          6         300
   2           400         NULL       NULL