Я хочу создать запрос, который может дать мне количество сообщений идентификатора пользователя + час, когда оно было отправлено.
У меня есть таблица часов с 0-23 часами, у меня есть таблица истории с сообщениями, которые пользователи опубликовали + дата_добавлена.
Теперь я хочу создать результат, который покажет мне:
Каждый час должен иметь ВСЕХ пользователей из пользовательской таблицы
Количество сообщений, отправленных этим пользователем, даже если в таблице истории нет записей с идентификатором пользователя
Я пробовал этот запрос, единственное, что неправильно, это то, что он не показывает каждый час userId
SELECT COUNT(*) AS messages, h.hour AS `Hour`, c.user_id, p.name
FROM history c
INNER JOIN users p ON c.user_id = p.user_id
RIGHT JOIN hours h ON h.hour = HOUR(c.`date_added`)
AND YEAR(`date_added`) = ' AND MONTH(`date_added`) = ༿' AND DAY(`date_added`) = ག'
GROUP BY h.hour, c.user_id
ORDER BY h.hour ASC;
Скрипка: http://sqlfiddle.com/#!9/5c60d5b
- РЕДАКТИРОВАТЬ спасибо за все запросы!
Всего 4 ответа
С кросс-объединением для hours
и users
и левым присоединением к history
:
select
t.hour, t.user_id, t.name, sum(history_id is not null) messages
from (select * from hours cross join users) t
left join (
select * from history
where
year(date_added) = 2019 and month(date_added) = 3 and day(date_added) = 6
) h
on h.user_id = t.user_id and t.hour = hour(h.date_added)
group by t.hour, t.user_id, t.name
Смотрите демо .
Результаты:
> hour | user_id | name | messages
> .. |........ |...... |.........
> 14 | 4 | test4 | 0
> 15 | 1 | test | 0
> 15 | 2 | test2 | 0
> 15 | 3 | test3 | 0
> 15 | 4 | test4 | 0
> 16 | 1 | test | 1
> 16 | 2 | test2 | 0
> 16 | 3 | test3 | 5
> 16 | 4 | test4 | 8
> 17 | 1 | test | 0
> .. |........ |...... |.........
Я добавил user_id = 4
в таблицу users
ваших примеров данных, потому что они были включены в history
.
Для получения всех возможных комбинаций между двумя несвязанными таблицами вы можете использовать CROSS JOIN
. Таким образом, запрос ниже достигает: «Каждый час должен иметь ВСЕХ пользователей из таблицы пользователей» :
SELECT h.hour, u.user_id
FROM hours h
CROSS JOIN users u
Попробуйте использовать левое присоединение и время до даты в группе по
SELECT COUNT(*) AS messages, hour(date_added) , c.user_id, p.name
FROM history c
INNER JOIN users p ON c.user_id = p.user_id
LEFT JOIN hours h ON h.hour = hour(date_added)
AND YEAR(`date_added`) = ' AND MONTH(`date_added`) = ༿' AND DAY(`date_added`) = ག'
GROUP BY hour(date_added) , c.user_id
ORDER BY h.hour ASC;
Если вы все-таки пользователи всех часов, независимо от того, есть у них сообщения или нет, приведенный ниже код, вероятно, то, что вы ищете. Он содержит два подзапроса, позволяющих добавить таблицу подсчета сообщений в полный список пользователей и часов.
SELECT messages, h.hour AS `hour`, h.user_id, h.name
FROM (
SELECT COUNT(*) AS messages, HOUR(c.`date_added`) as `hour`, c.user_id, p.name
FROM users p
INNER JOIN history c
ON c.user_id = p.user_id
WHERE YEAR(`date_added`) = ' AND MONTH(`date_added`) = ༿' AND DAY(`date_added`) = ག'
GROUP BY HOUR(c.`date_added`), c.user_id, p.name
) z
right join
(
SELECT h.hour, u.user_id, u.name
FROM hours h
CROSS JOIN users u
) h ON h.hour = z.hour
AND h.user_id = z.user_id
ORDER BY hour