MYSQL - выберите строку на основе условия, в противном случае выберите другую строку

У меня есть таблица товары, которая связана с product_prices. Каждый продукт может иметь несколько цен для разных стран.

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

Logic

  1. If product has price for the specified country-> then country price will be displayed.
  2. If product does not have price for the specified country-> then price for with country_id 400 needs to be displayed.
  3. If product has no price for specified country and for country with id 400 -> then the price with country_id 500 needs to be displayed.
  4. If product has not price for specified country and id 400, and id 500 -> then price which has country with biggest amount of total users will be displayed.

Я сделал что-то подобное для одного продукта. Но я не знаю, как добиться этого для всех продуктов.

select p.id, pp.price, pp.country_id,
(CASE WHEN (pp.country_id=:countryId) then 0 else
    (CASE WHEN (pp.country_id=400) then 1 else
        (case when (pp.country_id=500) then 2 else 3 END) END) END) as priorityIndex
from products p
inner join product_prices pp on p.id = pp.product_id
inner join countries c on pp.country_id = c.id
where p.id = '00057c218b154d5b838b928a0189ff9f'
order by priorityIndex limit 1;

Мои данные: Таблица страны

идентификатор_страныкод странывсего_пользователей
2Франция10
10НАС100
27Великобритания200
400Евросоюз160
500ГЛОБАЛЬНЫЙ150

Таблица цен на продукцию

Код товарацена_idценаидентификатор_страны
продукт119,9927
продукт120,99400
продукт130,99500
продукт2199,9910
продукт2299,99400
продукт350,99500
продукт4402
продукт44510

Мой ожидаемый результат при вставке страны 27:

Код товараценаидентификатор_страны
продукт119,9927
продукт220,99400
продукт350,99500
продукт44510

Я сделал что-то вроде этого. Но я не уверен только в последней части. В случае, если цена продукта должна быть выбрана на основе общего числа дилеров.

select A.id, A.price_id, A.price, A.country from
(select p.id, pp.id as price_id, pp.price, pp.country_id as country,
        ROW_NUMBER() over (PARTITION BY p.id order by (CASE WHEN (pp.country_id=:countryId) then 0 else
            (CASE WHEN (pp.country_id=400) then 1 else
                (case when (pp.country_id=500) then 2 else 3 END) END) END), c.total_dealer_users desc) AS rowNumber
 from products p
          inner join product_prices pp on p.id = pp.product_id
          inner join countries c on pp.country_id = c.id
 where p.id in ('00057c218b154d5b838b928a0189ff9f','054a8caf911e4ff3a594990af20a9611')) as A
where rowNumber=1;

поэтому вы, вероятно, захотите взглянуть на функцию row_number() javatpoint.com/mysql-номер_строки-функция. разделите по вашему продукту и упорядочите по вашей бизнес-логике. затем просто выберите строки, где rownum = 1

Bryan Dellinger 22.04.2022 20:19

@BryanDellinger Я просмотрел документацию и попробовал что-то вроде этого:

Michael Bat 22.04.2022 21:46

выберите A.id, A.price_id, A.price, A.country, A.rowNumber из (выберите p.id, pp.id как price_id, pp.price, pp.country_id как страну, ROW_NUMBER() над (PARTITION BY p.id) AS rowNumber from products p внутреннее соединение product_prices pp on p.id = pp.product_id внутреннее объединение стран c на pp.country_id = c.id порядок по (CASE WHEN (pp.country_id=:countryId) затем 0 иначе ( CASE WHEN (pp.country_id=400), затем 1 else (случай, когда (pp.country_id=500), затем 2 else 3 END) END) END)) как A;

Michael Bat 22.04.2022 21:46

@BryanDellinger, но rowNumber мне ничем не помог. Он не делает раздел правильно. Я пытался использовать раздел по pp.country_id, а также по pp.id, но все равно ничего. Номер строки 1 всегда не совпадает с полями, которые мне нужно получить

Michael Bat 22.04.2022 21:48

Версия MySQL @MatBailie — 8.0.26

Michael Bat 22.04.2022 22:43
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
1
5
69
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

В MySQL 8.0+ ROW_NUMBER() позволяет удалять все строки, кроме первой для каждого продукта:

SELECT * FROM (
    SELECT p.id, 
        pp.price,
        pp.country_id,
        ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY FIELD(pp.country_id, 500, 400, 27) DESC) AS `rn`
    FROM products AS p
        JOIN product_prices AS pp
            ON p.id = pp.product_id
    WHERE p.id IN (...)
) AS t WHERE t.rn=1
Ответ принят как подходящий
SELECT
  *
FROM
(
  SELECT
    pp.product_id,
    pp.price_id,
    pp.price,
    pp.country_id as country,
    ROW_NUMBER() OVER (
      PARTITION BY pp.product_id
          ORDER BY FIELD(
                     c.country_id,
                     27,
                     400,
                     500,
                     c.country_id
                   ),
                   c.total_users DESC
    )
      AS rowNumber
  FROM
    product_price   pp
  INNER JOIN
    country         c
      ON pp.country_id = c.country_id
)
  AS ranked_price
WHERE
  rowNumber=1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=94f8851be7f309d8c4fd1946230d7999

Другие вопросы по теме