Як перевірити базове знання sql, elwood - s blog
Днями згадав про одну задачу, яка мені попалася на роботі півтора року тому.
У той час я ще не знав про 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 елементів.
От і все.
Алгоритм розв'язання задачі.
Ну ясно ж - треба зробити 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