Mysql - 无法从别名值获取具有最大值的正确行

Question

我有一个价格表，其中包含我的产品的所有价格。产品可以有不同的价格，具体取决于:国家/地区、客户群体、货币、日期间隔...

这是我的表“commerce_product_price_index”，其中包含一个示例:1 个产品有 2 个价格。

+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+
| id | product_id | customer_group_id | currency_id | country_id | geozone_id | user_id | time_from  | time_to    | price |
+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+
| 1  | 1          | 0                 | 0           | 0          | 0          | 0       | 1488632584 | 1646348400 | 500   |
| 2  | 1          | 0                 | 3           | 19         | 7          | 0       | 1488632584 | 1646348400 | 700   |
+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+

对于每一行，我计算的“分数”取决于:country_id、geozone_id、user_id、currency_id ...。这些值是在客户登录之前获取的，因此不要介意这一点。

我计算分数的请求示例(我没有在 WHERE 中添加条件来简化示例):

SELECT `cppi`.*, 
IF (1488788495 >= `time_from` AND 1488788495 <= `time_to`, 1, 0) + 
IF (`customer_group_id` = 2, 2, 0) + IF (`geozone_id` = 7, 4, 0) + 
IF (`country_id` = 19, 8, 0) + IF (`currency_id` = 3, 16, 0)+ 
IF (`user_id` = 0, 32, 0) AS score 
FROM `commerce_product_price_index` AS `cppi` 

现在我的结果中有一个新字段=>“分数”。

+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+----------+
| id | product_id | customer_group_id | currency_id | country_id | geozone_id | user_id | time_from  | time_to    | price | score    |
+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+----------+
| 1  | 1          | 0                 | 0           | 0          | 0          | 0       | 1488632584 | 1646348400 | 500   | 33       |  
| 2  | 1          | 0                 | 3           | 19         | 7          | 0       | 1488632584 | 1646348400 | 700   | 61       |
+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+----------+

第一行的分数 => 33

第二行的分数 => 61

因此，我决定根据此请求获取得分最高的行。

SELECT `ccpi_max_score`.*, MAX(score) AS max_score 
FROM (
  SELECT `cppi`.*, 
  IF (1488788495 >= `time_from` AND 1488788495 <= `time_to`, 1, 0) +
  IF (`customer_group_id` = 2, 2, 0) + 
  IF (`geozone_id` = 7, 4, 0) + 
  IF (`country_id` = 19, 8, 0) + 
  IF (`currency_id` = 3, 16, 0)+ 
  IF (`user_id` = 0, 32, 0) AS score 
  FROM `commerce_product_price_index` AS `cppi`
) AS ccpi_max_score 
GROUP BY product_id

结果如下:它返回一行，但价格错误。我得到价格为“500”的行，而不是价格为“700”的行。

+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+----------+-----------------+
| id | product_id | customer_group_id | currency_id | country_id | geozone_id | user_id | time_from  | time_to    | price | score    | max_score       |
+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+----------+-----------------+
| 1  | 1          | 0                 | 0           | 0          | 0          | 0       | 1488632584 | 1646348400 | 500   | 33       | 61              |
+----+------------+-------------------+-------------+------------+------------+---------+------------+------------+-------+----------+-----------------+

我认为这是 GROUP BY 的问题。但我是 SQL 新手，我不知道我的错误在哪里。

我创建了一个 fiddle => http://sqlfiddle.com/#!9/5a25f9/4/0

谢谢。

Answer 1

SELECT product_id,customer_group_id,currency_id,country_id,geozone_id,user_id,time_from, time_to,price,max(score) as max_score
FROM (
  SELECT `cppi`.*, 
  IF (1488788495 >= `time_from` AND 1488788495 <= `time_to`, 1, 0) +
  IF (`customer_group_id` = 2, 2, 0) + 
  IF (`geozone_id` = 7, 4, 0) + 
  IF (`country_id` = 19, 8, 0) + 
  IF (`currency_id` = 3, 16, 0)+ 
  IF (`user_id` = 0, 32, 0) AS score 
  FROM `commerce_product_price_index` AS `cppi`
) AS ccpi_max_score 
GROUP BY product_id

您需要重写所有列，并且不能使用.*，除非最大值是可以连接到表的unique_id