Я думаю, что я понял это. Огромное спасибо Алексу Рису за то, что он был правлением, и за этот ответ за обеспечение основы для получения стабильных результатов. Я поставил свой окончательный ответ и мой процесс получения его ниже.
Окончательный ответ
> SELECT lc1.id, lc1.label, mc.max_cnt
FROM (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
) lc1
INNER JOIN (
SELECT id, label, max(cnt) as max_cnt
FROM (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
) lc2
GROUP BY label COLLATE UTF8_GENERAL_CI
) mc
ON lc1.cnt = mc.max_cnt
AND lc1.label LIKE mc.label COLLATE UTF8_GENERAL_CI;
+----+--------+-----+
| id | label | cnt |
+----+--------+-----+
| 1 | foobar | 3 |
| 6 | blah | 3 |
| 7 | Stuff | 1 |
+----+--------+-----+
Вы можете увидеть SQLFiddle этого здесь: http://www.sqlfiddle.com/#!9/19bb0/1
Процесс достижения этого
Вычислительный счет
Первым шагом было просто вычислить, сколько раз использовался каждый ярлык. Это довольно просто:
> SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id;
+----+--------+-----+
| id | label | cnt |
+----+--------+-----+
| 1 | foobar | 3 |
| 2 | FooBar | 1 |
| 3 | fooBar | 0 |
| 4 | Foobar | 1 |
| 5 | BLAH | 1 |
| 6 | blah | 3 |
| 7 | Stuff | 1 |
+-------------+-----+
Получение максимального количества
Затем мне нужно отсортировать метки по убыванию количества, и из каждого набора меток, которые пишутся одинаково, но с разной капитализацией, взять верхнюю:
> SELECT id, label, max(cnt)
FROM (subquery ORDER BY cnt) AS s
GROUP BY label COLLATE UTF8_GENERAL_CI;
Это приводит к запросу, который выглядит следующим образом:
> SELECT id, label, max(cnt)
FROM (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
ORDER BY cnt DESC;
) AS s
GROUP BY label COLLATE UTF8_GENERAL_CI;
+----+--------+----------+
| id | label | max(cnt) |
+----+--------+----------+
| 1 | foobar | 3 |
| 6 | blah | 3 |
| 7 | Stuff | 1 |
+----+--------+----------+
Это выглядит правильно! И это ... почти.
Почему это не работает
Оказывается, MySQL не гарантирует, что GROUP BY выполняет стабильную сортировку. Именно из-за изменений / внутренней реализации, выполнение ORDER BY в подзапросе, за которым следует GROUP BY во внешнем запросе, приводит к тому, что строка, отсортированная сверху, по-прежнему находится сверху. И SELECT id, label, max(cnt) не гарантирует, что идентификатор и метка, которые он захватывает, будут из той же строки, что и max(cnt). Например, простое изменение ORDER BY в подзапросе приводит к тому же возвращаемому количеству, но неправильным меткам:
> SELECT id, label, max(cnt)
FROM (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
ORDER BY cnt ASC;
) AS s
GROUP BY label COLLATE UTF8_GENERAL_CI;
+----+--------+----------+
| id | label | max(cnt) |
+----+--------+----------+
| 1 | fooBar | 3 |
| 6 | BLAH | 3 |
| 7 | Stuff | 1 |
+----+--------+----------+
Поэтому нам нужен способ избежать этого.
Более стабильное решение
К счастью, я нашел другой ответ, который объясняет, как сделать что-то похожее на то, что я хочу. Уловка в том, что в этой задаче значение, которое они хотят максимально (временная метка), уже находится в таблице. Пока в этой задаче мне нужно подсчитать количество.
Поэтому я первым делом создал таблицу, содержащую всю необходимую мне информацию, а затем сослался на нее так же, как в другом ответе:
CREATE TABLE LabelCounts (
`id` INT, `label` VARCHAR(80), `cnt` INT
);
INSERT INTO LabelCounts (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
);
SELECT *
FROM LabelCounts lc1
INNER JOIN (
SELECT id, label, max(cnt) as max_cnt
FROM LabelCounts
GROUP BY label COLLATE UTF8_GENERAL_CI
) lc2
ON lc1.cnt = lc2.max_cnt
AND lc1.label LIKE lc2.label COLLATE UTF8_GENERAL_CI;
Это делает работу. Вы можете видеть, что он правильно пересчитывает правильную метку, независимо от того, что возвращает внутренний запрос, присоединяясь к числу, равному максимальному количеству.
К сожалению, я не собирался выполнять это в среде, где я могу просто создавать дополнительные таблицы. И если я вместо этого использовал CREATE TEMPORARY TABLE , я не могу ссылаться на него дважды, как мне нужно в этом запросе. Таким образом, последним шагом было создание таблицы LabelCounts. Вы можете увидеть полученный запрос в верхней части этого ответа.
