AlexMaxTM
@AlexMaxTM

Как сделать группировку запроса с пустыми записями?

Добрый день, специалисты по mysql
Есть такой запрос
SELECT user_id, mode, SUM(f1) as f1, SUM(f2) as f2
FROM table1
WHERE mode IN (1,2)
GROUP BY user_id, mode
ORDER BY user_id, mode

При наличии данных запрос работает как нужно.
user_id		mode		f1	f2
1		1		100	200
1		2		300	400
2		1		200	300
2		2		200	300

Но иногда нет каких то данных для поля mode. Например, у user - 1 нет значений mode=2, и получается, что-то типа такого:
user_id		mode		f1	f2
1		1		100	200
2		1		200	300
2		2		200	300

А нужно чтобы при отсуствии данных было таким образом:
user_id		mode		f1	f2
1		1		100	200
1		2		0	0
2		1		200	300
2		2		200	300

Пробовал поиграться с LEFT OUTER JOIN но ничего не получилось.
Может есть у кого идеи, как исправить запрос, чтобы получился нужный результат?
  • Вопрос задан
  • 209 просмотров
Решения вопроса 1
erge
@erge
Примус починяю
Необходимо было чтобы в правой таблице, которая райтджойнится, были ВСЕ значения по которым вы хотите получить данные и которые группируются, т.е. в ней должны быть все user_id и все возможные (нужные для выборки) mode
для этого делается что?
делается декартово произведение (cross join), чтобы получить такую "матрицу"
venn-cross-join1.png

SELECT * FROM
  (SELECT user_id FROM tbl GROUP BY user_id) u,
  (SELECT 1 as mode UNION SELECT 2) m
;


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

в итого запрос будет такой:
SELECT t2.user_id, t2.mode, SUM(COALESCE(f1,0)) as f1, SUM(COALESCE(f2,0)) as f2
  FROM tbl t1
  RIGHT JOIN (
    SELECT * FROM
      (SELECT user_id FROM tbl GROUP BY user_id) u,
      (SELECT 1 as mode UNION SELECT 2) m
    ) t2 ON t2.user_id = t1.user_id AND t2.mode = t1.mode
  GROUP BY t2.user_id, t2.mode
  ORDER BY t2.user_id, t2.mode
;


работающий пример на sqlfiddle

как-то так.

UPD
PS:

скорее всего имеется некая таблица users, тогда в кроссджойн не надо использовать саму же таблицу (SELECT user_id FROM tbl GROUP BY user_id) u1, а к таблице user закроссить (SELECT 1 as mode UNION SELECT 2) m и залефтджойнить подопытную таблицу.

получим как-то так:

SELECT u.user_id, u.name, m.mode, SUM(COALESCE(f1,0)) as f1, SUM(COALESCE(f2,0)) as f2
  FROM
    users u
  CROSS JOIN (SELECT 1 as mode UNION SELECT 2) m
  LEFT JOIN tbl t ON u.user_id = t.user_id AND m.mode = t.mode
  GROUP BY u.user_id, u.name, m.mode
;


кроме того, в таком случае можно выводить имена пользователей, так же для пользователей по которым вообще нет записей в исследуемой таблице, будут выводится 0

см. пример на sqlfiddle
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
Planet_93
@Planet_93
Вот если повторить ваш пример.

5db2da8f34f13879344056.jpeg

То результат ожидаемый.

5db2daa5672e5451429163.jpeg

Если у вас user, mode и f1,f2 в отдельных таблицах, то вам необходимо взять таблицу связки user и mode и уже к ней соединять остальные данные.

Если таблицы связи user и mode нету, не понятно откуда взять user 1 и mode 2 если по нему нет записей?
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы