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

Question

[Алексей]

Добрый день, специалисты по 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 но ничего не получилось.
Может есть у кого идеи, как исправить запрос, чтобы получился нужный результат?

Answer 1

[Сергей c0re]

Необходимо было чтобы в правой таблице, которая райтджойнится, были ВСЕ значения по которым вы хотите получить данные и которые группируются, т.е. в ней должны быть все user_id и все возможные (нужные для выборки) mode
для этого делается что?
делается декартово произведение (cross join), чтобы получить такую "матрицу"

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

Comments

[Алексей]

Спасибо, что так глубоко погрузились в вопрос.
К, сожалению, это всё не то что нужно, все ваши варианты не решают задачу. Но это не ваша вина, так как вы не знаете все нюансы.
Отмечаю решение, так как другим может пригодится.

Answer 2

[Planet_93]

Вот если повторить ваш пример.



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



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

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

Comments

[Алексей]

Выборка идет только из одной таблицы. У вас на первом скришоте ошибка. В таблице вообще нет данных для user=1 и mode=2, но надо составить запрос так, чтобы всегда присутствовал mode 1 и 2 для каждого user_id. И если записей нет, то сумма по всем полям равна 0.

[Сергей c0re]

Алексей, прикол в том, что MySQL когда в SELECT перечислено поле не указанное в GROUP BY туда просто подставляет значение от балды!
поэтому группировать необходимо все поля перечисленные в SELECT , которые не внутри группирующих функций count, sum, и пр. пр.
другие СУБД , когда в SELECT есть поле не перчисленное в GROUP BY выдают просто ошибку.