我有两张价格和物品的table。一个商品的价格表可以包含多行。我需要为每件商品找到最合适的价格。我的第一个尝试是：
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在subselect中，我计算每个价格行的排名，以便以后我可以按此排名排序。结果是：
<!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>你可以看到，我有两个价格的物品变种\u id 1830现在排名190和90。但我只需要190级的价格。当我加上
GROUP BY price_list.article_variant_id然后排名190的那一行消失了。
有什么想法吗？
1条答案
按热度按时间arknldoa1#
尝试了一下，找到了使用max（）的解决方案：
结果似乎就是我搜索的结果：