Три интересные задачи на знание SQL

Веб разработка - Работа с БД

Буквально несколько часов назад, после внимательного изучения комментариев вот к этому топику я был несколько удивлен что многие не очень любят использовать условия HAVING и похоже другие возможности СУБД. Скажу прямо — мне очень понравилась обсуждение простейшего и не работающего запроса, в результате которого многие узнали для себя много нового и познавательного (это мое личное мнение). Поэтому я решил сделать вот что — вывести три моих самых любимых задачи по знанию SQL (MySQL) на суд общественности.

Данные задачи абсолютно реальные, и в отдаленном светлом прошлом действительно имели место, и были благополучно решены жертвой некоторого количества рабочего времени и нервных клеток. Я уверен на 100% что они актуальны и по сей день, и будут актуальны еще долго. Сложными их назвать нельзя, но и очень простыми тоже (в зависимости от уровня подготовки конечно).



Итак… Честно говоря, мне очень интересно узнать, кто как их решит. У меня конечно есть решения, но все же признаю — что мой корыстный интерес узнать как решили бы их профи SQL, которые я думаю еще не вымерли. Да и всем будет интересно я думаю… Словом — очень бы хотелось чтоб каменты рулили. :-)

Условие.

Галереи картинок состоит из 3-х простых таблиц. Есть некоторое количество категорий, в каждую из которых входят некоторое количество фотоальбомов, в которые свою очередь в входят тучи фоток цикла «Йа слева». Так уж вышло, что существующую структуру менять нельзя, (да это было бы и не интересно).

-- Категории
CREATE TABLE `photo_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255), -- название
`is_published` tinyint(1), -- флаг 1/0 - опубликовано/не опубликовано
`ordi` int(11), -- порядок сорировки, простое число от 1 до ...
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

-- Галереи
CREATE TABLE `photo_gallery` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c_id` int(11), -- ID категории
`title` varchar(255), -- название
`is_published` tinyint(1), -- флаг 1/0 - опубликовано/не опубликовано
`ordi` int(11), -- порядок сорировки, простое число от 1 до ...
PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

-- Фотографии
CREATE TABLE `photo_image` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`g_id` int(11), -- ID галереи
`title` varchar(255), -- название фотографии (Йа слева)
`is_published` tinyint(1), -- флаг 1/0 - опубликовано/не опубликовано
`is_main_foto` tinyint(1), -- флаг 1/0 - главная фотография/обычная.
`ordi` int(11) DEFAULT NULL, -- порядок сорировки, простое число от 1 до ...
PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

* This source code was highlighted with Source Code Highlighter.

Условия применимые ко всем задачам:

1) по флагу is_published должен исключаться сам объект и все в него входящие объекты. То есть, если на категорию is_published = 0 то все альбомы и соответственно все их фотки должны исключаться (причем уже пофигу опубликованы они или нет).

2) Все условия сортировки должны быть по ключам ordi.

3) Для всех задач нельзя показывать пустые галереи и категории, то есть те категории в которых нет галерей и те галереи в которых нет категорий.

4) В галерее только одна фотография может быть отмечена как главная или ни одной. Если даже такая фота is_published = 0 она все равно показывается, если помимо нее есть еще фоты с is_published = 1

5) Еще добавлю — условия боевые, то есть все запросы должны быть как можно проще и работать как можно быстрее, количество запросов должно быть сведено к минимуму.

Да, я намеренно исключил из таблиц малозначащие поля и индексы, чтоб не отвлекаться на мелочи :-)

А теперь сами задачи (по сложности в порядке возрастания):

Задача первая

Дано ID категории. Нужно написать запрос (один!) который бы получал все галереи этой категории, для каждой из которых получал ID главной фотографии, а если таковой нет — то ID какой-нибудь входящей в категорию (все равно, лишь бы была фота).

Задача вторая

Дано ID фоты. Если хотите — так же ID галереи. Требуется с минимум усилий определить следующую/предидущую фоту в порядке по ordi. (напоминаю, что тут только по ordi принимать решение нельзя так как следующая/предидущая может быть и is_published = 0, таким образом надо взять ближайшую которая is_published = 1). Задача решается 2-мя запросами, я уверен что можно решить и одним (без UNION) но у меня не получилось. Если у кого получится тому респект и уважуха. :-)

Задача третья

Это самая жесть. Дано некоторое число N. Требуется вывести список категорий, и количество последних альбомов в них, причем для каждой категории это количество не должно быть больше N и отсортировано в порядке убывания по ordi. То есть допустим 3 категории, в 1-ой 10 фоток, во второй 25, а в третей только три. Нужно чтоб на выходе было для первой 5 последних (с наибольшими ordi отсортированных по убыванию), для второй 5 (аналогично) и для 3-ей — 3 (аналогично). Плюс условие первой задачи, то есть надо еще главную фоту для галереи или какую-нибудь еще.

У меня есть решения всех трех задач и я обязательно опубликую их но… попозже и с объяснениями что, как и почему. :-) И еще — для многих на первый взгляд это покажется просто неимоверно сложным — на самом деле решения достаточно просты, хотя и включают в себя некоторые «редкоиспользуемые» (по мнению некоторых) конструкции SQL типа того же преславутого HAVING.

СУБД — MySQL 5. Обходиться без хранимых процедур и функций. Удачи! :-)