Как сделать запрос с разными условями?

Question

[DevAs27]

Всем привет. Есть два таблица device и price

sqlfiddle.com/#!9/1043a7

Условия запроса такой
- в ответе должен быть лишь один вариант цены. Вариант возвращаемой цены выбираем по типу (выше значение - приоритетнее), то есть:
Если у девайса есть цена только с типом 1 - возвращаем цену от type: 1
Если у девайса есть цена с типами 1 или 2 - возвращаем цену от type: 2
Если у девайса есть цена с типами 1, 2 и 3 - возвращаем цену от type: 3
Если у девайса есть цена с типами 1 и 3 - возвращаем цену от type: 3

Пример на выходе должно быть

{
    "id": 1,
    "price": {
      "type": 1, 
      "price_b": 100,
      "price_c": 80,
      "price_d": 20 
    },
}

как сделать такой запрос?

Comments

[Adamos]

Сджойнить девайсы с ценами дважды: сначала с группировкой для выяснения максимального значения, потом по условию совпадения значения с максимальным.

[DevAs27]

Adamos, думаю не проще ли сделать в коде?) Берешь все цены девайса и логика в коде c условями который нужно.

[Adamos]

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

Answer 1

[Василий Банников]

можно что-то типа такого:

SELECT d.id, name, p.type, pp.price_b, pp.price_c, pp.price_d FROM device d
INNER JOIN (SELECT device_id, MAX(type) as "type" FROM price GROUP BY device_id) p ON p.device_id = d.id
INNER JOIN price pp ON pp.type = p.type AND pp.device_id = d.id

Но он не выдаёт товары без цены. Можно попробовать сделать LEFT JOIN, чтобы выводило товары без цены.

Версия от ChatGPT

Выглядит страшно, но работает

SELECT d.id, 
    CASE 
        WHEN p3.type IS NOT NULL THEN p3.type 
        WHEN p2.type IS NOT NULL THEN p2.type 
        WHEN p1.type IS NOT NULL THEN p1.type 
    END AS type, 
    CASE 
        WHEN p3.type IS NOT NULL THEN p3.price_b 
        WHEN p2.type IS NOT NULL THEN p2.price_b 
        WHEN p1.type IS NOT NULL THEN p1.price_b 
    END AS price_b, 
    CASE 
        WHEN p3.type IS NOT NULL THEN p3.price_c 
        WHEN p2.type IS NOT NULL THEN p2.price_c 
        WHEN p1.type IS NOT NULL THEN p1.price_c 
    END AS price_c, 
    CASE 
        WHEN p3.type IS NOT NULL THEN p3.price_d 
        WHEN p2.type IS NOT NULL THEN p2.price_d 
        WHEN p1.type IS NOT NULL THEN p1.price_d 
    END AS price_d 
FROM device d 
LEFT JOIN (SELECT device_id, type, price_b, price_c, price_d 
           FROM price WHERE type = 3) p3 ON d.id = p3.device_id 
LEFT JOIN (SELECT device_id, type, price_b, price_c, price_d 
           FROM price WHERE type = 2) p2 ON d.id = p2.device_id 
LEFT JOIN (SELECT device_id, type, price_b, price_c, price_d 
           FROM price WHERE type = 1) p1 ON d.id = p1.device_id

Comments

[DevAs27]

Ваш вариант вроде работает сейчас проверю.

Gpt как всегда не правильно работает если убрать 3 тип из price.

Да смотрел через chatgpt выглядел страшно)

[Василий Банников]

DevAs27, на самом деле не такой уж и страшный запрос:
Делается три джоина по device_id и типу.
В селекте делается switch case, который смотрит на то, какие цены нашлись.
Самое страшное тут - это множество джоинов,

[DevAs27]

sqlfiddle.com/#!9/1d5529/1

[Василий Банников]

DevAs27, ???

[DevAs27]

Василий Банников, проверил все , ваш вариант работает.

Answer 2

[Rsa97]

Если я правильно понял вопрос:

SELECT `d`.*, `p`.*
  FROM (
    SELECT `device_id`, MAX(`type`) AS `type`
      FROM `price`
    ) AS `t`
    JOIN `price` AS `p`
      ON `p`.`device_id` = `t`.`device_id`
        AND `p`.`type` = `t`.`type`
    RIGHT JOIN `device` AS `d`
      ON `d`.`id` = `p`.`device_id`