MySQL SELECT, самый частый группой
Превосходное Буферный Проводник , быть добрался, чтобы быть такой сильной памятью мышц, которую я желаю, что я мог использовать его в других приложениях. Я нахожу, что он чрезвычайно быстр, активно редактируя больше чем два файла.
3 ответа
SELECT t1.*
FROM (SELECT tag, category, COUNT(*) AS count
FROM tags INNER JOIN stuff USING (id)
GROUP BY tag, category) t1
LEFT OUTER JOIN
(SELECT tag, category, COUNT(*) AS count
FROM tags INNER JOIN stuff USING (id)
GROUP BY tag, category) t2
ON (t1.tag = t2.tag AND (t1.count < t2.count
OR t1.count = t2.count AND t1.category < t2.category))
WHERE t2.tag IS NULL
ORDER BY t1.count DESC;
Я согласен, что это слишком много для одного запроса SQL. Любое использование GROUP BY внутри подзапроса заставляет меня вздрагивать. Вы можете сделать его выглядеть проще, используя представления:
CREATE VIEW count_per_category AS
SELECT tag, category, COUNT(*) AS count
FROM tags INNER JOIN stuff USING (id)
GROUP BY tag, category;
SELECT t1.*
FROM count_per_category t1
LEFT OUTER JOIN count_per_category t2
ON (t1.tag = t2.tag AND (t1.count < t2.count
OR t1.count = t2.count AND t1.category < t2.category))
WHERE t2.tag IS NULL
ORDER BY t1.count DESC;
Но в основном он выполняет ту же работу за кулисами.
Вы комментируете, что вы можете легко выполнить аналогичную операцию в коде приложения. Так почему бы тебе этого не сделать? Выполните более простой запрос, чтобы получить количество по категории:
SELECT tag, category, COUNT(*) AS count
FROM tags INNER JOIN stuff USING (id)
GROUP BY tag, category;
И отсортируйте результат в коде приложения.
SELECT tag, category
FROM (
SELECT @tag <> tag AS _new,
@tag := tag AS tag,
category, COUNT(*) AS cnt
FROM (
SELECT @tag := ''
) vars,
stuff
GROUP BY
tag, category
ORDER BY
tag, cnt DESC
) q
WHERE _new
По вашим данным это возвращает следующее:
'automotive', 8
'ba', 8
'bamboo', 8
'bananatree', 8
'bath', 9
Вот тестовый сценарий:
CREATE TABLE stuff (tag VARCHAR(20) NOT NULL, category INT NOT NULL);
INSERT
INTO stuff
VALUES
('automotive',8),
('ba',8),
('bamboo',8),
('bamboo',8),
('bamboo',8),
('bamboo',8),
('bamboo',8),
('bamboo',10),
('bamboo',8),
('bamboo',9),
('bamboo',8),
('bamboo',10),
('bamboo',8),
('bamboo',9),
('bamboo',8),
('bananatree',8),
('bananatree',8),
('bananatree',8),
('bananatree',8),
('bath',9);
(Изменить: забыл DESC в ORDER BY)
Легко сделать с LIMIT в подзапросе. Есть ли в MySQL по-прежнему ограничение без LIMIT-in-subqueries? В примере ниже используется PostgreSQL.
=> select tag, (select category from stuff z where z.tag = s.tag group by tag, category order by count(*) DESC limit 1) AS category, (select count(*) from stuff z where z.tag = s.tag group by tag, category order by count(*) DESC limit 1) AS num_items from stuff s group by tag;
tag | category | num_items
------------+----------+-----------
ba | 8 | 1
automotive | 8 | 1
bananatree | 8 | 4
bath | 9 | 1
bamboo | 8 | 9
(5 rows)
Третий столбец необходим только в том случае, если вам нужен счетчик.