Mysql Подвыбор рейтинга

Question

Mysql Подвыбор рейтинга

У меня есть две таблицы с ценами и статьями. Таблица цен может содержать несколько строк для одного товара. Мне нужно найти лучшую цену для каждой статьи. Моя первая попытка:

SELECT price_list.id, price_list.ranking, price_list.article_variant_id, price_list.price
FROM (
    SELECT 
        CASE WHEN price.merchant_id             = 955 THEN 100 ELSE 0 END
     + CASE WHEN price.invoice_merchant_id = 983 THEN 90 ELSE 0 END
     + CASE WHEN price.merchant_group_id    = 12345 THEN 80 ELSE 0 END
     + CASE WHEN price.country_id           = 101 THEN 70 ELSE 0 END
     + CASE WHEN price.order_flag_id        = 12345 THEN 60 ELSE 0 END as ranking, price.*
    FROM article_price_special price
    INNER JOIN article_variant ON article_variant.id = price.article_variant_id
    WHERE price.currency_id = 11 
        AND (price.active_from IS NULL OR price.active_from IS NOT NULL AND price.active_from < NOW())
        AND (price.active_to IS NULL OR price.active_to IS NOT NULL AND price.active_to > NOW())
) as price_list
JOIN article_price_special outer_prices ON outer_prices.article_variant_id IN (1835,1838,2068,1982,1830) AND outer_prices.id = price_list.id
WHERE ranking > 0
#GROUP BY price_list.article_variant_id
ORDER BY ranking DESC

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
  <head>
    <title>article_price_special</title>
    <meta name="GENERATOR" content="HeidiSQL 9.3.0.4984">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <style type="text/css">
      thead tr {background-color: ActiveCaption; color: CaptionText;}
      th, td {vertical-align: top; font-family: "Tahoma", Arial, Helvetica, sans-serif; font-size: 8pt; padding: 3px; }
      table, td {border: 1px solid silver;}
      table {border-collapse: collapse;}
      thead .col0 {width: 64px;}
      .col0 {text-align: right;}
      thead .col1 {width: 63px;}
      .col1 {text-align: right;}
      thead .col2 {width: 111px;}
      .col2 {text-align: right;}
      thead .col3 {width: 51px;}
      .col3 {text-align: right;}
    </style>
  </head>

  <body>

    <table caption="article_price_special (5 rows)">
      <thead>
        <tr>
          <th class="col0">id</th>
          <th class="col1">ranking</th>
          <th class="col2">article_variant_id</th>
          <th class="col3">price</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td class="col0">7172</td>
          <td class="col1">190</td>
          <td class="col2">1830</td>
          <td class="col3">66</td>
        </tr>
        <tr>
          <td class="col0">116</td>
          <td class="col1">90</td>
          <td class="col2">1835</td>
          <td class="col3">72</td>
        </tr>
        <tr>
          <td class="col0">117</td>
          <td class="col1">90</td>
          <td class="col2">1838</td>
          <td class="col3">66</td>
        </tr>
        <tr>
          <td class="col0">115</td>
          <td class="col1">90</td>
          <td class="col2">1830</td>
          <td class="col3">66</td>
        </tr>
        <tr>
          <td class="col0">3577</td>
          <td class="col1">70</td>
          <td class="col2">2068</td>
          <td class="col3">32</td>
        </tr>
      </tbody>
    </table>

  

  </body>
</html>

Вы можете видеть, что у меня есть две цены для article_variant_id 1830 сейчас с рангом 190 и 90. Но мне нужна только цена с рангом 190. Когда я добавляю

    GROUP BY price_list.article_variant_id

затем строка с рангом 190 исчезает.

Есть идеи?

0

mysql sql-subselect

Источник

user5884988 14 июн '18 в 12:17

1 ответ

Другие вопросы по тегам mysql sql-subselect

user5884988 14 июн '18 в 12:25 2018-06-14 12:25 · Answer 1 · 2018-06-14 12:25

Пробовал и нашел это решение с max():

SELECT price_list.id, MAX(price_list.ranking), price_list.article_variant_id, price_list.price
FROM (
    SELECT 
        CASE WHEN price.merchant_id             = 955 THEN 100 ELSE 0 END
     + CASE WHEN price.invoice_merchant_id = 983 THEN 90 ELSE 0 END
     + CASE WHEN price.merchant_group_id    = 12345 THEN 80 ELSE 0 END
     + CASE WHEN price.country_id           = 101 THEN 70 ELSE 0 END
     + CASE WHEN price.order_flag_id        = 12345 THEN 60 ELSE 0 END as ranking, price.*
    FROM article_price_special price
    WHERE price.currency_id = 11 
        AND (price.active_from IS NULL OR price.active_from IS NOT NULL AND price.active_from < NOW())
        AND (price.active_to IS NULL OR price.active_to IS NOT NULL AND price.active_to > NOW())
) as price_list
JOIN article_price_special outer_prices ON outer_prices.article_variant_id IN (1835,1838,2068,1982,1830) AND outer_prices.id = price_list.id
WHERE ranking > 0
GROUP BY outer_prices.article_variant_id
ORDER BY ranking DESC

результат, кажется, тот, который я искал:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
  <head>
    <title>article_price_special</title>
    <meta name="GENERATOR" content="HeidiSQL 9.3.0.4984">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <style type="text/css">
      thead tr {background-color: ActiveCaption; color: CaptionText;}
      th, td {vertical-align: top; font-family: "Tahoma", Arial, Helvetica, sans-serif; font-size: 8pt; padding: 3px; }
      table, td {border: 1px solid silver;}
      table {border-collapse: collapse;}
      thead .col0 {width: 64px;}
      .col0 {text-align: right;}
      thead .col1 {width: 138px;}
      .col1 {text-align: right;}
      thead .col2 {width: 111px;}
      .col2 {text-align: right;}
      thead .col3 {width: 51px;}
      .col3 {text-align: right;}
    </style>
  </head>

  <body>

    <table caption="article_price_special (4 rows)">
      <thead>
        <tr>
          <th class="col0">id</th>
          <th class="col1">MAX(price_list.ranking)</th>
          <th class="col2">article_variant_id</th>
          <th class="col3">price</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td class="col0">115</td>
          <td class="col1">190</td>
          <td class="col2">1830</td>
          <td class="col3">66</td>
        </tr>
        <tr>
          <td class="col0">116</td>
          <td class="col1">90</td>
          <td class="col2">1835</td>
          <td class="col3">72</td>
        </tr>
        <tr>
          <td class="col0">117</td>
          <td class="col1">90</td>
          <td class="col2">1838</td>
          <td class="col3">66</td>
        </tr>
        <tr>
          <td class="col0">3577</td>
          <td class="col1">70</td>
          <td class="col2">2068</td>
          <td class="col3">32</td>
        </tr>
      </tbody>
    </table>



  </body>
</html>