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

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

ps: чем обусловлено требование «только 1 запрос»? задачи жизненные — давайте и условия будут жизненные (уточню: в жизни не всегда «меньше запросов — лучше»)

для первой задачи:
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


sql-ex.ru/ на хабре? :) Ох сколько я времени там убил… Затягивает то как

На первую задачу у меня получился такой запрос:

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 (исользует ли индексы), если нет, то возможно нужно переписать по-другому.

Для второй задачи можно сделать
ORDER BY abs(?-ordi) LIMIT 2

Но это будет медленно работать…

Для второго задания:
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


> не очень любят использовать условия HAVING

Если не ошибаюсь, многие сталкиваются с условием HAVING не из-за большой любви к SQL, а потому что без него в некоторых обстоятельствах просто не обойтись.

т.е. подзапросы разрешены, но запрос должен быть один? =) я, конечно, дилетант, но как mysql кеширует подобные запросы? Ведет ли кеш подзапросных таблиц?

а не быстрее ли будет выполнить допустим 3 запроса по индексу из каждой таблицы чем один запрос сложной структуры? поиграться конечно с заковырками можно, а вот надо ли?

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

Захожите на sql-ex, там вас научат даже регулярки стандартными средствами SQL-синтаксиса решать. Реально отбивает охоту решать все SQL-задачи на годы.

Сейчас я побрюзжу про отсутствие FOREIGN KEY. Все, побрюзжал, начинаю решать.

третья, остальные не интересно

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.

первая:

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

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;

А вообще, по-хорошему, если сделать вместо is_main_foto у фотографии main_photo_id и first_photo_id у галереи, то огромной части всего этого SQL-геморроя можно было бы избежать.

В первой задаче я бы презрел нормализацию таблиц и у галереи появилось бы поле «тайтловая фото».
Во второй —
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;

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

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

Надо написать SQL-запрос, который выдаст вероятности выпадения каждой из возможных сумм чисел при N бросках (N — переменная).

Ща хабр решит, что сегодня массово пытаються пропихнуть иньекции в него))))

> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

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

Красиво в данном случае — это не когда 1 запрос вместо 10, красиво — это когда работает максимально быстро…

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

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

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

И все таки, с чем связано неизменение структуры?
>да это было бы и не интересно
Не интерестно работать со структурой, которая очевидно не наилучшая. В которой приходиться городить костыли вместо простых решений

первая задача
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
третью просто некогда, пардон

Давайте уточним — подзапрос считается за запрос?

Кстати, а где обещанное авторское решение задач?

Навскидку, вторая задача запросом без 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

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) Тут явно одним запросом не обойтись, пока идей нет. Хотелось бы посмотреть вариант автора.