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

    image

    Буквально несколько часов назад, после внимательного изучения комментариев вот к этому топику я был несколько удивлен что многие не очень любят использовать условия 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. Обходиться без хранимых процедур и функций. Удачи! :-)

    UPD: Решения вот здесь. Там набралось для целого топика, что я и сделал.
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 82

      +9
      на таких небольших задачах (а три таблицы — это очень небольшая задача) себе же дешевле переписать один раз код и перевести базу в пригодный для нормальной работы вид.

      ps: чем обусловлено требование «только 1 запрос»? задачи жизненные — давайте и условия будут жизненные (уточню: в жизни не всегда «меньше запросов — лучше»)
        0
        В реальности было намного больше условий, выборок и так далее. Приходилось еще считать количество коментраиев, определять рейтинг, тэги, имена пользователей создавших галерею или фоту… Вощем, было нехило, но дело не в этом. Тут главное — способ решения. Решать по разному можно, подзапросами, джойнами и так далее…

        1 запрос — это значит что решается спокойно 1-м запросом. Просто надо додуматься «как». Впрочем, в этом случае думать-то особо не надо.
          0
          HAVING без аггрегирующих функций и группировки (который я вижу в первом ответе, который почти верный) уже наводит о мыслях, что что-то делается не так. это к вопросу о «спокойно решается 1 запросом».

          ps: вы не считаете, что спортивные задачи на умение составлять запросы прикольнее решать на нормальных структурах, а не на «структурах из жизни»? :-)
        +2
        для первой задачи:
        SELECT
          gal.*,
          (
            SELECT img.id FROM photo_image as img WHERE img.g_id = gal.id
            ORDER BY img.is_main_foto DESC, img.ordi LIMIT 1
          ) as main_photo_id
        FROM
          photo_category as cat
        INNER JOIN
          photo_gallery as gal
          ON
          gal.c_id = cat.id
        WHERE
          cat.id = $catId
          AND
          cat.is_published = 1
          AND
          gal.is_published = 1
        HAVING
          main_photo_id IS NOT NULL
        ORDER BY
          gal.ordi

          0
          Хорошо, а если ни одна фота не отмечена главной что будет? Впрочем начало неплохое…
            0
            Будет выбрана первая нескрытая в порядке сортировки.
              0
              пардон, невнимателен. Предыдущий ответ неверен :)
              0
              В подзапросе условие будет выглядеть так:
              WHERE img.g_id = gal.id AND ( img.is_main_foto =1 OR img.is_published = 1)
                0
                Теперь вроде верно.
              0
              Это уже не один запрос, ибо подзапрос — поже запрос
              +11
              sql-ex.ru/ на хабре? :) Ох сколько я времени там убил… Затягивает то как
                0
                Спасибо за ссылку
                +1
                На первую задачу у меня получился такой запрос:
                SELECT gal . * , (
                  SELECT id
                  FROM photo_image im
                  WHERE im.g_id = gal.id
                  AND im.is_published = TRUE
                  ORDER BY im.is_main_foto, im.ordi
                  LIMIT 1
                )im_id
                FROM photo_gallery gal
                WHERE gal.c_id = ?
                AND gal.is_published = TRUE
                HAVING im_id IS NOT NULL
                ORDER BY gal.ordi


                * This source code was highlighted with Source Code Highlighter.

                Это если предполагать что выбранная категория опубликована, иначе ещё с таблицей категорий связывать надо.
                P.S. Не помню, оптимизирует ли Mysql запросы вида ORDER… LIMIT 1 (исользует ли индексы), если нет, то возможно нужно переписать по-другому.
                  0
                  Этого не нужно было предполагать. Категория может быть и не опубликована. Да, связь с таблицей категорий обязательна.
                  0
                  Для второй задачи можно сделать
                  ORDER BY abs(?-ordi) LIMIT 2

                  Но это будет медленно работать…
                    0
                    Хотя если в галерее в среднем фотографий немного, то будет быстро.
                      +1
                      Я тоже так думал, но проблема в том, что если передан id первой или последней фотки, то результат не будет удовлетворять условию.
                      +3
                      Для второго задания:
                      SELECT
                          img.*
                      FROM
                          photo_image as img
                      WHERE
                          img.ordi > ( SELECT img2.ordi FROM photo_image as img2 WHERE img2.id = ? LIMIT 1)
                          AND
                          img.is_published = 1
                          AND
                          img.g_id = ?
                      LIMIT 1

                        0
                        Неа. Если так — это очень просто, да, и думать не надо. Нужно чтоб выполнялись условия. То есть если данная фота фходит в галерею или категорию которые НЕ опубликованы, то результат должен быть нулевым.
                        0
                        > не очень любят использовать условия HAVING

                        Если не ошибаюсь, многие сталкиваются с условием HAVING не из-за большой любви к SQL, а потому что без него в некоторых обстоятельствах просто не обойтись.
                          0
                          т.е. подзапросы разрешены, но запрос должен быть один? =) я, конечно, дилетант, но как mysql кеширует подобные запросы? Ведет ли кеш подзапросных таблиц?
                            –1
                            никак не кеширует. и практически не оптимизирует.
                            –1
                            imho подзапросы зло.
                            на практике оно зачастую работает медленнее чем 2 отдельных запроса.
                              0
                              Вы так категоричны.
                              Только если подзапрос зависит от внешнего (коррелирующий).
                                0
                                100%. Для подзапросов MySQL часто забивает на индексы, даже если индекс тривиально можно построить
                                +1
                                а не быстрее ли будет выполнить допустим 3 запроса по индексу из каждой таблицы чем один запрос сложной структуры? поиграться конечно с заковырками можно, а вот надо ли?

                                как говорит макконел — если я слышу словосочетание «хитрый код», то сразу думаю что код плохой ибо код должен быть простым и легок в понимании
                                  0
                                  угу, указал это же в первом комментарии.

                                  ps: у макконнелла куда более «хитрая» фамилия :-)
                                  0
                                  Захожите на sql-ex, там вас научат даже регулярки стандартными средствами SQL-синтаксиса решать. Реально отбивает охоту решать все SQL-задачи на годы.
                                    0
                                    Сейчас я побрюзжу про отсутствие FOREIGN KEY. Все, побрюзжал, начинаю решать.
                                      0
                                      Второе (без юниона, но жесть, т.к. как я понял, надо выбрать и следующую, и предыдущую):

                                      select
                                      ( select i.id
                                       from photo_image i
                                       where i.is_published = 1
                                        and i.g_id = :g_id
                                        and i.ordi < (select x.ordi from photo_image x where x.id = :id)
                                       limit 1
                                      ) as prv,
                                      ( select i.id
                                       from photo_image i
                                       where i.is_published = 1
                                        and i.g_id = :g_id
                                        and i.ordi > (select x.ordi from photo_image x where x.id = :id)
                                       limit 1
                                      ) as nxt;


                                      * This source code was highlighted with Source Code Highlighter.

                                      В оракле гораздо проще, но по условию нельзя :-(
                                      select
                                       lag(i.id, 1) over (order by ordi) prv,
                                       lead(i.id, 1) over (order by ordi) nxt
                                      from photo_image i
                                      where i.is_pubilshed = 1
                                       and i.g_id = :g_id
                                       and i.id = :id;


                                      * This source code was highlighted with Source Code Highlighter.
                                        0
                                        SELECT *
                                        FROM
                                            image LEFT JOIN folder USING (folder_id)
                                                 LEFT JOIN cathegory USING (cathegory_id)
                                        WHERE
                                            image.folder_id = (SELECT folder_id FROM images WHERE image_id=@image_id) AND
                                            folder.is_published = 1 AND
                                            cathegory.is_published = 1
                                        ORDER BY
                                            ABS(ordi - (SELECT ordi FROM images WHERE image_id=@image_id))
                                        LIMIT 2;


                                        * This source code was highlighted with Source Code Highlighter.

                                        Или я что-то не учёл? :)
                                          0
                                          По поводу сортировки по модулю разницы: я выше уже писал, что «если передан id первой или последней фотки, то результат не будет удовлетворять условию».
                                          Где image.is_pulbished = 1?
                                          LEFT JOIN здесь не нужен, здесь достаточно INNER JOIN.
                                            0
                                            SELECT *
                                            FROM
                                                image JOIN folder USING (folder_id)
                                                     JOIN cathegory USING (cathegory_id)
                                            WHERE
                                                image.folder_id = (SELECT (@folder_id:=folder_id) FROM images WHERE image_id=@image_id) AND
                                                image.is_published = 1 AND
                                                folder.is_published = 1 AND
                                                cathegory.is_published = 1
                                            ORDER BY
                                                ABS(ordi - (SELECT (@ord := ordi) FROM images WHERE image_id=@image_id))
                                            LIMIT
                                            SIGN((SELECT count(ordi) FROM images WHERE folder_id=@folder_id AND ordi>@ord))
                                            +
                                            SIGN((SELECT count(ordi) FROM images WHERE folder_id=@folder_id AND ordi<@ord));


                                            * This source code was highlighted with Source Code Highlighter.


                                            ((( Но mysql вычислять LIMIT не разрешает. Тогда можно включить так:
                                            SELECT images.*,
                                                (SELECT count(ordi) FROM images WHERE folder_id=(SELECT (@folder_id:=folder_id) FROM images WHERE image_id=@image_id) AND ordi>(SELECT (@ord := ordi) FROM images WHERE image_id=@image_id)) as next,
                                                (SELECT count(ordi) FROM images WHERE folder_id=(SELECT (@folder_id:=folder_id) FROM images WHERE image_id=@image_id) AND ordi<(SELECT (@ord := ordi) FROM images WHERE image_id=@image_id)) as prev
                                            FROM
                                                image JOIN folder USING (folder_id)
                                                     JOIN cathegory USING (cathegory_id)

                                            WHERE
                                                image.folder_id = @folder_id AND
                                                image.is_published = 1 AND
                                                folder.is_published = 1 AND
                                                cathegory.is_published = 1
                                            ORDER BY
                                                ABS(images.ordi - @ord)
                                            LIMIT 2;


                                            * This source code was highlighted with Source Code Highlighter.

                                            и парсить пыхой.
                                              0
                                              Дурня.
                                              Так лучше:
                                              SELECT images.*,
                                                  (images.ordi - (SELECT (@ord:=ordi) FROM images WHERE image_id=@image_id)) as destination
                                              FROM
                                                  image JOIN folder USING (folder_id)
                                                       JOIN cathegory USING (cathegory_id)

                                              WHERE
                                                  image.folder_id = (SELECT (@folder_id:=folder_id) FROM images WHERE image_id=@image_id) AND
                                                  image.is_published = 1 AND
                                                  folder.is_published = 1 AND
                                                  cathegory.is_published = 1
                                              ORDER BY
                                                  ABS(ordi - @ord)
                                              LIMIT 2;


                                              * This source code was highlighted with Source Code Highlighter.
                                                0
                                                Я от особенностей MySQL далек довольно, можно объяснить, что значит «SELECT (@ord:=ordi) FROM images ...» (интересует то, что в скобках)? А то я как-то сходу не смог понять.
                                                  0
                                                  Использование локальной переменной. Без этого 3-ю задачу решить очень трудно (точнее я так и не смог прдумать как это сделать).
                                                    0
                                                    Создание переменной @ord, которой присваивается значение столбца ordi.
                                                      0
                                                      SELECT (@folder_id:=folder_id) FROM images LIMIT 1
                                                      обозначает, что мы возвращаем folder_id, но так же записываем его в переменную @folder_id.

                                                      Есть ещё конструкция
                                                      SELECT folder_id FROM images LIMIT 1 INTO @folder_id

                                                      Здесь значение просто запришеться в @folder_id, но не будет результатом выборки.
                                            0
                                            Первое (громоздко вышло, выше было решение, которое мне самому больше понравилось):
                                            select g.id, g.title, i.id, i.title
                                            from photo_category c
                                             inner join photo_gallery g
                                              on g.c_id = c.id
                                             inner join photo_image i
                                              on i.g_id = g.id
                                             inner join photo_image i_1
                                              on i_1.g_id = i.g_id
                                            where (i.is_main_foto = 1
                                              or i.is_published = 1)
                                              and i_1.is_main_foto = 0
                                              and i_1.is_published = 1
                                              and g.is_published = 1
                                              and c.is_published = 1
                                              and c.id = :id
                                            group by g.id, g.title, i.id, i.title
                                            having count(i.id) > 1
                                              or (count(i.id) <= 1
                                              and count(i_1.id) > 0)
                                            order by
                                             g.ordi,
                                             i.ordi;


                                            * This source code was highlighted with Source Code Highlighter.
                                              0
                                              Скажите, а зачем «group by g.id, g.title, i.id, i.title» и туча условий having?..
                                            +2
                                            третья, остальные не интересно
                                            SELECT
                                                c_id
                                                , g_id
                                                , (
                                                    SELECT id
                                                    FROM photo_image p 
                                                    WHERE
                                                        is_published = 1
                                                        AND p.g_id = yy.g_id
                                                    ORDER BY is_main_foto DESC, ordi
                                                    LIMIT 1
                                                ) p_id
                                            FROM
                                                (
                                                    SELECT
                                                        c.id c_id
                                                        , g.id g_id
                                                        , IF(
                                                            @typex = g.c_id
                                                            , @rownum := @rownum + 1
                                                            , @rownum := 1 + LEAST(0, @typex := g.c_id)
                                                        ) AS rown
                                                    FROM
                                                        photo_category AS c
                                                        INNER JOIN photo_gallery AS g ON g.c_id = c.id
                                                        , (SELECT @rownum := 1, @typex := '_') zz
                                                    WHERE
                                                         c.is_published = 1
                                                         AND g.is_published = 1
                                                    ORDER BY c.ordi, g.ordi DESC
                                                ) yy
                                            WHERE rown <= 5

                                            * This source code was highlighted with Source Code Highlighter.
                                              0
                                              как минимум не выполняется условие (4) — не выберется фотка с is_main_foto=1, is_published = 0
                                                +1
                                                согласен, просмотрел
                                                Выше уже написали вариант: WHERE (is_main_foto = 1 OR is_published = 1)…
                                                0
                                                Запрос не учитывает возможную пустоту галереи, + в этом случае WHERE (is_main_foto = 1 OR is_published = 1) ничего не даст. Если не считать этого, то ваше — единственное предложенное верное решение 3-ей задачи.
                                                  0
                                                  пустые галереи лечатся, например, через:
                                                  GROUP BY 1, 2 HAVING p_id
                                                  в самом конце запроса, но это уже мелочи
                                                +1
                                                первая:

                                                select g.id, ifnull(
                                                (select id from photo_image i where i.i.g_id=g.id and is_main_foto=1),
                                                (select id from photo_image i where i.i.g_id=g.id and is_published=1 limit 1)
                                                ) as photo_id
                                                from photo_gallery g
                                                where g.c_id = ...
                                                and g.is_published = 1
                                                and exists(select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
                                                order by g.ordi


                                                комментарий: в photo_image проиндексировать g_id
                                                или хотя бы одну из пар (g_id, is_published) (g_id, is_main_foto)

                                                вторая:

                                                select (select i2.id from photo_image i2
                                                where i1.g_id = i2.g_id and i2.ordi > i1.ordi
                                                and (i2.is_published = 1 or i2.is_main_foto = 1)
                                                order by i2.ordi asc limit 1) as next_id,
                                                (select i2.id from photo_image i2
                                                where i1.g_id = i2.g_id and i2.ordi < i1.ordi
                                                and (i2.is_published = 1 or i2.is_main_foto = 1)
                                                order by i2.ordi desc limit 1) as prev_id
                                                from photo_image i1 where i1.id = ...


                                                комментарий: проиндексировать (g_id, ordi)

                                                третья:

                                                select c.id as c_id, g.id as g_id, ifnull(
                                                (select id from photo_image i where i.i.g_id=g.id and is_main_foto=1),
                                                (select id from photo_image i where i.i.g_id=g.id and is_published=1 limit 1)
                                                ) as photo_id
                                                from photo_category c
                                                join photo_gallery g on g.c_id = c.id
                                                where c.is_published=1 and g.is_published=1
                                                and exists(select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
                                                and g.id in (select g2.id from photo_gallery g2 where g2.c_id = c.id order by ordi desc limit ...)
                                                order by c.ordi desc, g.ordi desc


                                                комментарий: поможет составной индекс (c_id asc, ordi desc) у photo_gallery
                                                  0
                                                  заодно приложите эксплейны, если вас не затруднит?

                                                  >> комментарий: поможет составной индекс (c_id asc, ordi desc) у photo_gallery
                                                  с каких пор mysql [b]умеет[/b] в составных индексах задавать порядок?
                                                    0
                                                    эксплейны только представляю в голове, на реальной базе не пробовал. для 3й задачи возможно лучше:

                                                    select c.id as c_id, g.id as g_id, ifnull(i.id,
                                                    (select id from photo_image i where i.i.g_id=g.id and is_published=1 limit 1)
                                                    ) as photo_id
                                                    from (select id, (select min(ordi) as min_ordi
                                                    from (select ordi from photo_gallery g
                                                    where g.c_id = c.id and g.is_published=1
                                                    and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
                                                    order by ordi desc limit ...))
                                                    from photo_category c where c.is_published=1) c
                                                    join join photo_gallery g on g.c_id = c.id and g.is_published=1 and g.ordi >= min_ordi
                                                    and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
                                                    left join photo_image i on i.g_id=g.id and is_main_foto=1
                                                    order by c.ordi desc, g.ordi desc
                                                      0
                                                      запрос не работает…
                                                        0
                                                        да, виноват, писал из головы, запускать даже не пробовал.
                                                        внутринний подзапрос, выбирающий ограничение на ordi должен быть таким:

                                                        select id, (select ordi from photo_gallery g
                                                        where g.c_id = c.id and g.is_published=1
                                                        and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
                                                        order by ordi desc limit 5, 1) x
                                                        from photo_category c where c.is_published=1


                                                        сейчас попробую проверить что не только ошибок нет, но и что на данных результат правильный
                                                          0
                                                          select c.id as c_id, g.id as g_id, ifnull(i2.id,
                                                          (select id from photo_image i3 where i3.g_id=g.id and is_published=1 limit 1)
                                                          ) as photo_id
                                                          from (
                                                          select c.id, c.ordi, (select ordi from photo_gallery g
                                                          where g.c_id = c.id and g.is_published=1
                                                          and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
                                                          order by ordi desc limit ..., 1) min_ordi
                                                          from photo_category c where c.is_published=1
                                                          ) c
                                                          join photo_gallery g on g.c_id = c.id and g.is_published=1
                                                          and (g.ordi > min_ordi or min_ordi is null)
                                                          and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
                                                          left join photo_image i2 on i2.g_id=g.id and i2.is_main_foto=1
                                                          order by c.ordi desc, g.ordi desc


                                                          вроде работает, в многоточие надо поставить нужное ограничение на число групп
                                                          P.S. внутренний конечно, self-fix
                                                          P.P.S. всю ночь без интернета просидел
                                                    0
                                                    2-ая

                                                    SELECT image.*

                                                    FROM photo_image as image, photo_gallery as gal, photo_category as cat

                                                    where image.g_id in (select gal2.id from photo_gallery as gal2 where gal2.id=gal.id)
                                                    and gal.id in (select cat2.id from photo_category as cat2 where cat2.id=cat.id)
                                                    and cat.is_published=1 and gal.is_published=1 and image.is_published=1
                                                    and image.ordi > (select min(image2.ordi) from photo_image as image2 where image2.id=0)

                                                    order by image.ordi

                                                    limit 1;
                                                      +1
                                                      А вообще, по-хорошему, если сделать вместо is_main_foto у фотографии main_photo_id и first_photo_id у галереи, то огромной части всего этого SQL-геморроя можно было бы избежать.
                                                        +1
                                                        В первой задаче я бы презрел нормализацию таблиц и у галереи появилось бы поле «тайтловая фото».
                                                        Во второй —
                                                        SELECT p.id FROM photo_image AS o LEFT JOIN photo_image AS p ON p.g_id=o.g_id AND p.ordi > o.ordi AND is_published = 1 WHERE o.id =? ORDER BY p.ordi LIMIT 1;

                                                          +1
                                                          Не так давно мне попалась одна интересная и невероятно сложная задача (pdf) по SQL:

                                                          Есть 20-гранная игральная кость с числами на ней (числа не от 1 до 20, они случайны и не повторяются).
                                                          Есть таблица, содержащая все числа с кости и вероятности их выпадения.

                                                          Надо написать SQL-запрос, который выдаст вероятности выпадения каждой из возможных сумм чисел при N бросках (N — переменная).
                                                            0
                                                            N раз перемножить декартово таблицу саму на себя (inner join), сложить, каждую сумму разделить на 20^n. на выходе — получим искомые p.
                                                              0
                                                              пардон:
                                                              1. забыл группировку по значению суммы
                                                              2. делить не сумму, а COUNT(*)
                                                                0
                                                                Так-то оно так, но это решение только для конкретного N. Есть как минимум 10 универсальных решений.
                                                                  0
                                                                  Упс, то есть 9. Я сам из них додумался бы только до одного.
                                                                0
                                                                Так не выйдет. N — переменная, которая передается в запрос, то есть запрос должен быть универсальным. Ограничений на использование возможностей БД нет.

                                                                Правда есть косяк, я тут посмотрел получше, по условию задачи (pdf-файлик), там не говорится про MySQL:
                                                                «Contestants may use any database technology at their disposal, but the submitted solutions should be compatible with at least one of the following database tech-nologies: Oracle 11g for Windows, SQL Server 2008, and DB2 9.5 for Windows».

                                                                То есть решение должно может быть написано для любой СУБД, но быть совместимым с Oracle 11g, SQL Server 2008 или DB2 9.5.
                                                                0
                                                                тоже одним запросом? :)
                                                                  0
                                                                  Да, одним запросом, но ограничения на использование возможностей СУБД состоит в том, что решение должно быть совместимым с Oracle 11g, SQL Server 2008 или DB2 9.5.
                                                                0
                                                                Ща хабр решит, что сегодня массово пытаються пропихнуть иньекции в него))))
                                                                • UFO just landed and posted this here
                                                                    0
                                                                    у меня тоже была иерархическая структура (фуры, палеты, коробки), и при неправильно статусе где-то выше нужно было исключать всё, что ниже

                                                                    я на триггерах/процедурах сделал

                                                                    если не хочется, чтобы снятие галочки с самой верхней папки автоматом сбрасывало галки со всех вложенных, то да, добавить второе поле is_parent_published, которое устанавливается триггером сверху.
                                                                    и вывод делается только в случае если is_published = 1 and is_parent_published = 1

                                                                    я так понимаю, изменения поля is_published происходят не очень часто, так что триггеры не будут особо напрягать базу, зато запросы на самом нижнем уровне делаются гораздо чаще, и джойны и вложенные запросы будут медленней работать.

                                                                    да и по is_published индекс бы не помешал, ведь выборка ведётся по этому полю, не?
                                                                    • UFO just landed and posted this here
                                                                    +2
                                                                    > ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

                                                                    MySQL лучше не грузить сложными запросами — два-три простых выполнятся быстрее, поэтому условие минимизации числа запросов не совсем понятно…

                                                                    Красиво в данном случае — это не когда 1 запрос вместо 10, красиво — это когда работает максимально быстро…
                                                                      0
                                                                      Зачача первая:
                                                                      select pg.id as PG_ID, pim.*, count(pim.id) as PI_IMG_NUMBER 
                                                                      	from photo_gallery pg inner join photo_image pim on (pg.id=pim.g_id) 
                                                                      	where pg.c_id=CATEGORY_ID and pg.is_published=1
                                                                      	group by pg.id 
                                                                      	having (PI_IMG_NUMBER > 0)
                                                                      	order by pg.ordi, pim.is_main_foto DESC, pim.is_published DESC, pim.ordi 


                                                                      Задача вторая:
                                                                      select pim.* 
                                                                      	from photo_image pim cross join photo_image pim2 
                                                                      		on (pim.g_id=pim2.g_id)
                                                                      	where pim2.ordi > pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID
                                                                      	limit 0, 1

                                                                      Для этого запроса можно уменьшить скорость выполнения, если заранее известна галерея
                                                                      select pim.* 
                                                                      	from photo_image pim cross join photo_image pim2 
                                                                      		on (pim.g_id=pim2.g_id and pim.g_id=GALLERY_ID)
                                                                      	where pim2.ordi > pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID
                                                                      	limit 0, 1


                                                                      Для третьей не совсем понятно что реально надо сделать. Либо вывести некоторое количество картинок по категориям, либо вывести некоторое количество галерей по категориям, в которых расположены картинки, и соотвественно показать количество картинок для каждой категории.
                                                                        +1
                                                                        Подправил первую задачу, так как inner join даст возможность не обращать внимания на существования внутри таблицы с картинками какой-либо категории (то есть выберутся все, которые существуют только в таблице с галереями). А про вариант, когда в галерее могут быть исключительно все неотображаемые картинки недосмотрел. Итоговый:
                                                                        select pg.id as PG_ID, pig.*
                                                                        	from photo_gallery pg inner join photo_image pim on (pg.id=pim.g_id) 
                                                                        	where pg.c_id='1' and pg.is_published=1 and (pim.is_published=1 or pim.is_main_foto=1)
                                                                        	group by pg.id 
                                                                        	order by pg.ordi, pim.is_main_foto DESC, pim.is_published DESC, pim.ordi
                                                                          0
                                                                          Оригинально, только категрия может быть и неопубликована, + я долго не мог понять что имелось ввиду под pig.*
                                                                            0
                                                                            pig.* => pim.* ;-)

                                                                            В запросе было pig, переносил — забыл поменять ;-)

                                                                            ЗЫ. более интересна реакция на решение второй задачи
                                                                              0
                                                                              Во второй задачи ошибочка, наверное надо (pim.id!=IMAGE_ID), а то смысла нет :)

                                                                              Получаем:
                                                                              select pim.*
                                                                              from photo_image pim cross join photo_image pim2
                                                                              on (pim.g_id=pim2.g_id and pim.g_id=1)
                                                                              where pim2.ordi < pim.ordi and pim2.is_published=1 and pim.id!=2
                                                                              limit 0, 1
                                                                                0
                                                                                Если по условию задачи: все элементы ordi разные для одной галереи.
                                                                                А если с чистой логики: не может один и тот же элемент (с одним и тем же id) иметь два разных значения ordi.

                                                                                Но ошибка действительно есть ;-) двойку потерял. Да и плюс, элементов в тестовую таблицу накидал парочку всего и просмотрел, что порядок ordi может не соблюдаться, то есть, элемент мы конечно найдём, но не обязательно тот, который действительно искали.

                                                                                Этот запрос на следущий элемент:
                                                                                select pim2.* 
                                                                                	from photo_image pim cross join photo_image pim2 
                                                                                		on (pim.g_id=pim2.g_id and pim.g_id=GALLERY_ID)
                                                                                	where pim2.ordi > pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID
                                                                                	order by pim2.ordi ASC
                                                                                	limit 0, 1


                                                                                Этот запрос на предыдущий:
                                                                                select pim2.* 
                                                                                	from photo_image pim cross join photo_image pim2 
                                                                                		on (pim.g_id=pim2.g_id and pim.g_id=GALLERY_ID)
                                                                                	where pim2.ordi < pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID
                                                                                	order by pim2.ordi DESC
                                                                                	limit 0, 1


                                                                                Кроме того, если уже хочется, чтобы было точно известно, что мы не должны получить тот же id фотографии (хотя и предыдущих запросов хватит), то можно добавить
                                                                                where pim2.ordi < pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID and NOT(pim.id=pim2.id)
                                                                                

                                                                        0
                                                                        И все таки, с чем связано неизменение структуры?
                                                                        >да это было бы и не интересно
                                                                        Не интерестно работать со структурой, которая очевидно не наилучшая. В которой приходиться городить костыли вместо простых решений
                                                                          0
                                                                          если говорить о первой задаче — то main_foto_id решил бы ее намного красивее и быстрее
                                                                          0
                                                                          первая задача
                                                                          SELECT g.id, i.id FROM photo_gallery AS g
                                                                          LEFT JOIN photo_category AS c ON c.id = g.c_id
                                                                          LEFT JOIN
                                                                          (SELECT id, g_id FROM photo_image ORDER BY is_main_foto DESC LIMIT 1) AS i
                                                                          ON i.g_id = g.id
                                                                          WHERE c.id = 1 AND i.id IS NOT NULL

                                                                          вторая задача
                                                                          SELECT id FROM photo_image AS i
                                                                          WHERE ordi > (SELECT ordi FROM photo_image WHERE id = 2)
                                                                          AND is_published > 0
                                                                          LIMIT 1
                                                                          третью просто некогда, пардон
                                                                            0
                                                                            Давайте уточним — подзапрос считается за запрос?
                                                                              0
                                                                              Кстати, а где обещанное авторское решение задач?
                                                                                0
                                                                                Сижу, готовлю сейчас. :-)
                                                                                0
                                                                                Навскидку, вторая задача запросом без UNION решается так:

                                                                                SELECT i2.*
                                                                                FROM photo_image i1
                                                                                INNER JOIN photo_image i2 ON (i2.is_published=1) AND (i1.g_id=i2.g_id) AND (i2.ordi > i1.ordi)
                                                                                WHERE i1.id=?
                                                                                ORDER BY i2.ordi
                                                                                LIMIT 1
                                                                                  0
                                                                                  1)
                                                                                  SELECT *, (
                                                                                  SELECT id
                                                                                  FROM photo_image AS pi
                                                                                  WHERE pi.g_id = pg.id
                                                                                  AND (
                                                                                  is_main_foto =1
                                                                                  OR is_main_foto =0
                                                                                  )
                                                                                  ORDER BY is_main_foto DESC
                                                                                  LIMIT 1
                                                                                  ) AS Image_id
                                                                                  FROM `photo_gallery` AS pg
                                                                                  WHERE `c_id` =2
                                                                                  AND `is_published` =1
                                                                                  HAVING Image_id IS NOT NULL
                                                                                  ORDER BY `ordi` DESC
                                                                                  2) Для случая, когда известно, что у фотки точно есть слейдующая и предыдушая придумал такое:
                                                                                  SELECT pi2.id, pi2.ordi, pi3.id, pi3.ordi
                                                                                  FROM `photo_image` AS pi
                                                                                  INNER JOIN `photo_image` AS pi2 ON pi.g_id = pi2.g_id
                                                                                  INNER JOIN `photo_image` AS pi3 ON pi2.g_id = pi3.g_id
                                                                                  WHERE pi.`id` =167
                                                                                  AND pi.`is_published` =1
                                                                                  AND pi2.`is_published` =1
                                                                                  AND pi3.`is_published` =1
                                                                                  AND pi2.id != pi.id
                                                                                  AND pi3.id != pi.id
                                                                                  AND pi3.ordi > pi.ordi
                                                                                  AND pi2.ordi < pi.ordi
                                                                                  ORDER BY `pi2`.`id` ASC, pi3.id DESC

                                                                                  Если фотка первая или вторая, то можно дополнительный запрос использовать на проверку граничных значений.

                                                                                  3) Тут явно одним запросом не обойтись, пока идей нет. Хотелось бы посмотреть вариант автора.
                                                                                    0
                                                                                    посмотрел решения, довольно интересно, спасибо

                                                                                  Only users with full accounts can post comments. Log in, please.