Вложенный запрос MySQL

197

13 апреля 2017, 17:09

Мне нужно вывести топ 5 названий комнат(таблица rooms) с помощью одного SQL запроса. Топ измеряется количеством уникальных пользователей(из таблицы publications), оставивших хоть одну публикацию в комнате.

Пытался сделать так:

SELECT name, (SELECT COUNT(DISTINCT author_id) FROM publications WHERE 
name_room = rooms_.name) AS count FROM rooms_ ORDER BY count DESC LIMIT 5

Но данный способ не работает. Как можно это реализовать?

Answer 1

Вложенный запрос тут не нужен. Достаточно сгруппировать всё по комнатам и подсчитать число уникальных авторов в каждой, отсортировать по этому числу и взять нужный вам лимит (в моём примере он = 3):

SELECT _rooms.id, _rooms.title, COUNT(DISTINCT _publications.author_id) n
FROM _rooms
INNER JOIN _publications ON _publications.room_id = _rooms.id
GROUP BY _rooms.id
ORDER BY n DESC
LIMIT 3;

Для отладки использовал тестовые данные:

CREATE TEMPORARY TABLE _rooms(id INT, title char(8))
  SELECT 1 id, 'room #1' title UNION
  SELECT 2 id, 'room #2' title UNION
  SELECT 3 id, 'room #3' title UNION
  SELECT 4 id, 'room #4' title UNION
  SELECT 5 id, 'room #5' title
;
CREATE TEMPORARY TABLE _publications(room_id INT, author_id INT, mess char(8))
  -- 2 uniq authors (III)
  SELECT 1 room_id, 1 author_id, 'foo' mess UNION ALL
  SELECT 1 room_id, 2 author_id, 'foo' mess UNION ALL
  -- 1 uniq authors
  SELECT 2 room_id, 1 author_id, 'foo' mess UNION ALL
  SELECT 2 room_id, 1 author_id, 'foo' mess UNION ALL
  SELECT 2 room_id, 1 author_id, 'foo' mess UNION ALL
  -- 3 uniq authors (II)
  SELECT 3 room_id, 1 author_id, 'foo' mess UNION ALL
  SELECT 3 room_id, 2 author_id, 'foo' mess UNION ALL
  SELECT 3 room_id, 3 author_id, 'foo' mess UNION ALL
  -- 1 uniq authors
  SELECT 4 room_id, 1 author_id, 'foo' mess UNION ALL
  -- 4 uniq authors (I)
  SELECT 5 room_id, 2 author_id, 'foo' mess UNION ALL
  SELECT 5 room_id, 2 author_id, 'foo' mess UNION ALL
  SELECT 5 room_id, 3 author_id, 'foo' mess UNION ALL
  SELECT 5 room_id, 3 author_id, 'foo' mess UNION ALL
  SELECT 5 room_id, 4 author_id, 'foo' mess UNION ALL
  SELECT 5 room_id, 4 author_id, 'foo' mess UNION ALL
  SELECT 5 room_id, 5 author_id, 'foo' mess UNION ALL
  SELECT 5 room_id, 5 author_id, 'foo' mess
;