На днях вспомнил об одной задаче, которая мне попалась на работе года полтора назад.
В то время я еще не знал об sql-ex.ru и мои познания в SQL были достаточно слабыми, на уровне
простейших селектов с inner join’ами. И, как я помню тогда, я не смог решить эту задачу без
дополнительного подзапроса. Сейчас, после нескольких десятков решенных задач на sql-ex.ru я
без проблем с ней справился, что навело меня на мысль о том, что это – неплохой тест на знание
азов SQL. В отличие от более сложных задач, эта задачка не требует большого количества времени, но
покрывает почти всё, что требуется для среднего программера в вопросе знания SQL, особенно если решать её в уме. Сразу отпадают вопросы наподобие “что такое JOIN, чем LEFT JOIN отличается от INNER JOIN, как работает группировка” итд.

Итак, формулировка такова. Есть 2 таблицы – Collection и Item.

Необходимо сделать выборку
Collection.ID Collection.Name Collection.Count
тех коллекций, которые содержат меньше 5 элементов.
Вот и всё.

ДАЛЬШЕ СПОЙЛЕР, НЕ ЧИТАТЬ !!!111 (Я не научился пока скрывать в вордпрессе куски постов, но как только научусь – оформлю соответствующе).

Алгоритм решения задачи.
Ну ясно же – надо сделать JOIN, делаем JOIN, группируем по Collection.ID, выводим ID, Name, Count(*).
Черт. При группировке по ID нельзя вывести Name. Значит, надо либо завернуть полученную выборку в еще 1 запрос, либо дописать в группировку этот Name. Ок, работает. Только пустые коллекции не выводятся. Почему ? Ну ясно же – они не попадают в результат INNER JOIN’a, надо использовать LEFT JOIN. О, теперь получилось. Только коллекции-то пустые, а count(*) выводит единицу вместо нуля. Ах да, это же LEFT JOIN, он джойнит Collection.ID Collection.Name NULL NULL и count(*) дает единицу. Как поправить-то. Вычесть 1 нельзя, поскольку так можно запороть непустые коллекции. Надо как-то проверить, является ли коллекция пустой, и для нее вывести 0, а для остальных – count(*). Да у нас же справа NULL, можно их в какую-нибудь агрегатную функцию запихнуть, и сравнить с NULL’ом. Если NULL, значит, коллекция пустая, и для нее выводим 0, иначе – count(*). Эврика! Ну и добавляем HAVING. Пишем запрос:

SELECT
  c.ID, c.Name, CASE WHEN COUNT(*) = 1 AND MAX(i.CollectionID) IS NULL THEN 0 ELSE COUNT(*) END
FROM Collection c
LEFT JOIN Item i ON c.ID = i.CollectionID
GROUP BY c.ID, c.Name
HAVING COUNT(*) < 5