mysql - 获取给定组的每种产品的最低价格

Question

我有这些简单的表格：

products

+----+------+---------+
| id | code | details |
+----+------+---------+
|  1 | P01  | ...     |
|  2 | P02  | ...     |
|  3 | P03  | ...     |
+----+------+---------+

prices

+----+---------------+-------------+--------+------------+------------+
| id | customerGroup | productCode | price  | date       | endDate    |
+----+---------------+-------------+--------+------------+------------+
|  1 | DEFAULT       | P01         | 1.2500 | 2018-01-01 | NULL       |
|  2 | DEFAULT       | P02         | 1.4000 | 2018-01-01 | NULL       |
|  3 | DEFAULT       | P03         | 2.0000 | 2018-01-01 | NULL       |
|  4 | DEFAULT       | P01         | 1.3000 | 2018-07-01 | NULL       |
|  5 | BLUE          | P01         | 1.3100 | 2019-01-01 | NULL       |
|  6 | BLUE          | P02         | 0.9000 | 2019-01-01 | 2019-05-01 |
|  7 | BLUE          | P03         | 2.0000 | 2019-09-01 | NULL       |
|  8 | DEFAULT       | P01         | 1.3200 | 2019-10-01 | NULL       |
|  9 | GREEN         | P01         | 0.5000 | 2019-10-01 | NULL       |
| 10 | GREEN         | P02         | 0.6000 | 2019-10-01 | NULL       |
| 11 | GREEN         | P03         | 0.7000 | 2019-10-01 | NULL       |
+----+---------------+-------------+--------+------------+------------+

逻辑

• prices是一张历史表。
• 每行代表一个price产品和一个客户组的记录。
• endDate = NULL表示price条目在和永恒之间有效date（除非出现以下情况）。
• 如果price相同的记录customerGroup并且productCode具有较新的匹配项 date，它将覆盖较旧的记录 - 即使price更高。
• price相同且匹配日期的最小值productCode将获胜；只要customerGroup它与给定的组匹配。

目标 1

要获得可能的最低价格记录：

• 对于组的成员BLUE和DEFAULT
• 对于产品P01
• 在日期2019-10-01

SELECT
    pp.*
FROM prices AS pp
JOIN (
    SELECT customerGroup, MAX(date) AS maxDate
    FROM prices AS pp
    WHERE productCode = 'P01'
    GROUP BY customerGroup
) AS eachRow ON (pp.customerGroup = eachRow.customerGroup AND pp.date = eachRow.maxDate)
WHERE 
    pp.productCode = 'P01'
    AND FIND_IN_SET(pp.customerGroup, 'DEFAULT,BLUE') > 0
    AND ((pp.endDate IS NULL AND '2019-10-01' >= pp.date) OR (pp.endDate IS NOT NULL AND ('2019-10-01' BETWEEN pp.date AND pp.endDate)))
GROUP BY pp.customerGroup
ORDER BY pp.price ASC
LIMIT 1;

这将返回正确/预期的单个结果：

+----+---------------+-------------+--------+------------+------------+
| id | customerGroup | productCode | price  | date       | endDate    |
+----+---------------+-------------+--------+------------+------------+
|  5 | BLUE          | P01         | 1.3100 | 2019-01-01 | NULL       |
+----+---------------+-------------+--------+------------+------------+

目标 2（问题）

如何一次获得每种产品的最低价格记录products？

结果应该是：

+----+---------------+-------------+--------+------------+------------+
| id | customerGroup | productCode | price  | date       | endDate    |
+----+---------------+-------------+--------+------------+------------+
|  2 | DEFAULT       | P02         | 1.4000 | 2018-01-01 | NULL       |
|  5 | BLUE          | P01         | 1.3100 | 2019-01-01 | NULL       |
|  7 | BLUE          | P03         | 2.0000 | 2019-09-01 | NULL       |
+----+---------------+-------------+--------+------------+------------+

笔记：

• 我正在寻找一种无需GROUP_CONCAT在外部查询中使用的方法。
• MIN+GROUP BY使用不是解决方案，因为它不会正确返回所有必要的字段，例如customerGroup和date...

这是一个SQL Fiddle。

标签： mysqlsqljoinselect

Answer 1

目标2（问题）如何一次获得产品中每种产品的最低价格记录？

答案是：

Select p.productcode, min(p.price)
from prices p
left join products prd on p.productcode = prd.code
Group by p.productcode

输出：P01 0.5 P02 0.6 P03 0.7

但是您的请求结果是按 id 分组的，因此无法按产品显示最低价格。您显示的结果中的实际数据实际上不是最低价格吗？（这令人困惑）。