SQL запрос, как?

Question

[max_rip]

Есть таблица

id|parentId|name|isMain

1|1|a1|null

2|1|a2|null

3|1|a3|null

4|2|b1|null

5|2|b2|1

6|2|b3|null

8|3|c1|1

9|3|c2|null

Надо отобрать записи так, чтоб с каждой группы parentId, была только одна строка с этим справляется GROP BY, но так чтоб если в группе был isMain 1, то отображалась она, если нет, то любой соответствующей группе.


В общем на выходе надо получить что-то из серии


1|1|a1|null

5|2|b2|1

8|3|c1|1

Comments

[Владимир Голованов]

А какая БД?

[max_rip]

Прошу прощения, mysql.

Answer 1

[m00t]

Вариант 1 — в лоб. Формально работает правильно (в оличие от первых двух ответов), но некрасиво:
SELECT * FROM test t1
WHERE
t1.is_main = (
SELECT MAX(is_main) FROM test t2 WHERE t2.parent_id = t1.parent_id
)
OR NOT EXISTS(SELECT * FROM test t2 WHERE t2.parent_id = t1.parent_id AND t2.is_main IS NOT NULL)
GROUP BY t1.parent_id


Comments

[m00t]

Немного подумав, так тоже будет работать, но избавляемся от ненужного запроса:

SELECT * FROM test t1
WHERE t1.is_main = 1 
OR NOT EXISTS(
  SELECT * FROM test t2 WHERE t2.parent_id = t1.parent_id AND t2.is_main IS NOT NULL
)
GROUP BY t1.parent_id

[max_rip]

Да этот запрос и так LEFT JOIN идет, смотрю на ваш ответ и не понимаю как его туда прикрутить +)

[m00t]

Простого способа решить эту задачу нету — полюбому придется как-то изворачиваться. Расскажите, что нужно сделать в общем (вместо с джойном) — может быть подскажем тут.

[max_rip]

Спасибо, я понял вашу мысль. Проще решить это на программном уровне. До этого был запрос типа
LEFT JOIN(select filename,galleryId from galleryBody WHERE imgMain=1) AS t1 ON product.galleryId = t1.galleryId
И он работает, но если пользователь не выбрал фото которое является главным, в альбоме. То ничего не выводилось на обложку.

[Acristi]

А как должен первый запрос работать? Group by вроде как намекает на требование перечисления в коде всех столюцов t1, а не только t1.parent_id

[max_rip]

Спасибо, переписал второй запрос под свои нужды, работает как надо ), смотрю пока.

[m00t]

Под эту задачу тогда вам не нужен group_by — можно сразу к списку товаров дополнительно селектить картинку:

SELECT *, (
  SELECT filename FROM galleryBody b WHERE p.galleryId = b.galleryId ORDER BY imgMain DESC LIMIT 1
) as filename FROM product p

[max_rip]

А что оптимальней, делать лефт джоин или селект как вы последний раз написали. Я не настолько хорошо разбираюсь в этом вопросе.

[m00t]

Плюс еще, если я правильно понимаю структуру вашей БД, я бы сделал galleryBody.imgMain типа BOOLEAN NOT NULL — тогда будет 1 и 0, а не 1 и NULL, что будет немного логичнее.

[m00t]

По вопросу оптимальности — обычно оптимальней будет left join. Но тут просто приджойнить табличку, насколько я вижу, не получится — т.е. уже под джойном будет запрос с подзапросом. А это уже хуже, чем просто один подзапрос. Ну, насколько я это понимаю. Плюс еще такое замечание — если у вас картинок меньше 10к, то вообще не стоит заморачиваться на производительности и выбрать самый легко читаемый (а поэтому и легко поддерживаемый) запрос.

[max_rip]

Ну с сожалению, я так понял класса BOOLEAN в mysql нет, это просто int размером в 1, я добавил по значение по умолчанию 0.
Погуглил
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered
После теперь соответствует логики.

[max_rip]

Ну да, тут до 10к картинок еще долго идти и скорее всего не дойдут. Спасибо за ваши комментарии.

[m00t]

да, BOOLEAN это синоним для TINYINT(1). Но в CREATE TABLE можно прямо писать BOOLEAN и MySQL это скушает. Главная мысль — это сделать это поле NOT NULL, чтобы были только 1 и 0 значения, потому что по логике NULL (значение отсутствует) не может быть у поля «является ли главным изображением», а может быть только true (1) и false (0)

[max_rip]

Теперь это NOT NULL, по умолчанию туда пишется 0.

Answer 2

[edogs]

select id, parentid, name, max(ismain) as max_ismain from tablename group by parentid;

Comments

[max_rip]

Превелико благодарствую.
Век живи, век учись +).

[m00t]

Вариант покажет для каждой группы случайную запись (имя и ID), но правильный is_main для этой группы

[max_rip]

Поспешил, плохо получается.
name выводится первый по порядку, в вот в isMain подставляется 1 если есть в групе. Получается
ответ такого вида
1|1|a1|null
5|2|b1|1
8|3|c1|1

[edogs]

Вам надо что бы «правильная» строка вытащилась?
Есть грязный способ:)
select max(concat_ws(':',if(ismain is null,0,1)id,parentid,name)) as abazaba from table_name group by parentid
и дальше уже разбирать результат.

[max_rip]

спасибо, грязный способ работает.

[max_rip]

А вот с реальными данным уже нет (

[edogs]

Если структура таблицы как у Вас описана в вопросе, то он не может не работать. Может быть что-то потеряли? Какая настоящая структура?

[edogs]

уточнение: при этом раскладе данные строки надо брать исключительно из max(concat_ws, только там они будут соответствовать друг другу.

[max_rip]

Структура точно такая же как и тут, единственное что в name идут названия файлов изображений, которые совершенно разные.

Answer 3

[Ivan Komarov]

Написал ради интереса попытки сэмулировать T-SQL ROW_NUMBER на MySQL:

SELECT DISTINCT t.nid, f.* FROM (
  SELECT 
    id,
    parentId,
    isMain,
    @nid := CASE 
      WHEN @parentId <> parentId 
      THEN id
      ELSE @nid
    END AS nid,
    @parentId := parentId AS t2
  FROM hfeed 
  ORDER BY parentID, isMain DESC
) AS t
INNER JOIN hfeed AS f ON f.id = t.nid

Answer 4

[Evengard]

Попробуйте ка
SELECT *, isMain IS NULL AS isnull FROM tablename ORDER BY isnull ASC GROUP BY parentId;

Comments

[max_rip]

ну для начала сначала идет групировка, а потом сортировка. И чем этот запрос отличается от

[max_rip]

ой случайно отправил
SELECT * FROM tablename GROUP BY parentId ORDER BY isMain ASC
?

Answer 5

[MikhailEdoshin]

SELECT g.parentId, d.id, d.name, d.isMain
  FROM (SELECT DISTINCT parentId
          FROM my_table) g,
  JOIN my_table d
    ON d.id == (
       SELECT o.id
         FROM my_table o
        WHERE o.parentId == g.parentId
     ORDER BY o.isMain DESC)

Нужен индекс по parentId, но он у вас, наверное, есть. И вот это вот == (SELECT ...) — это, наверное, особенность SQLite, там такая конструкция возвращает единственное значение. В другом диалекте может потребоваться писать что-то вроде:

    ON d.id IN (
       SELECT o.id
         FROM my_table o
        WHERE o.parentId == g.parentId
     ORDER BY o.isMain DESC
        LIMIT 1)

Answer 6

[Acristi]

Вот такой вариант может подойдет?
Расставить индексы и возможно еще кое что оптимизировать…

Select id, ParentId, name, isMain
FROM dbo.Cash
where isMain is not null
UNION ALL
SELECT x.*
FROM dbo. x
inner join
(
Select ParentId, MIN(id) as id
FROM dbo.Cash
where parentId not in (Select ParentId FROM dbo.Cash where isMain is not null)
group by ParentId
) y
on x.id = y.id
order by 2