MYSQL单个表中MAX和MIN之间的时间差

Question

我有一个如下所示的表，我正在尝试获取特定Id的MAX和MIN值，但前提是MAX输入日期大于MIN输入日期。

表：

ID  ENTRY_DATE              NAME       PRICE
1   2012-01-23 16:09:35     MONKEY     99.33
2   2012-01-23 11:04:09     MONKEY     97.65
3   2012-01-23 09:31:19     MONKEY     93.05
4   2012-01-23 15:12:14     DICE       30.49
5   2012-01-23 12:01:24     DICE       32.00
6   2012-01-23 08:01:24     DICE       31.00

所以我想做的是当MAX（price）entry_date大于MIN（price）entry_data时，获取MAX（prince）和MIN（prize）。根据上表，我的结果是：

NAME      MaxPrice     MinPrice   PriceDiff   
MONKEY    99.33        93.05      6.28

"骰子"不会显示，因为最大日期/时间发生在最小之前

我试过一个LEFT JOIN，但似乎想不通。这是我现在所拥有的，但它显然抓住了所有的结果，因为我似乎不知道如何比较参赛日期。。。

SELECT name,
MAX(price) as MaxPrice,
MIN(price) as MinPrice,
MAX(price)-MIN(price) AS PriceDiff,
FROM products
WHERE DATE(entry_date) = DATE(NOW()) 
GROUP BY name
ORDER BY PriceDiff DESC

谢谢你的帮助！

Answer 1

要选择每个组的最高价格（与之对应的日期），请执行以下操作：

SELECT p.NAME, p.ENTRY_DATE as dateofMax, p.PRICE as maxPrice
FROM products p
LEFT JOIN products p2
 ON p.NAME=p2.NAME AND p.PRICE<p2.PRICE
WHERE p2.PRICE IS NULL;

这样做是将products连接到name上的自身，在每个名称中形成每一对可能的价格，其中p.PRICE<p2.PRICE。由于这是一个左联接，如果在p中有一个价格，而在p2中没有更大的价格，则p2.PRICE将被设置为NULL。

因此，此查询选择没有更高价格的行，即的行，即最大价格（每组，为NAME）。

要选择每个组的最低价格（以及相应的日期），您可以在LEFT JOIN中将<更改为>，因此我们选择在表中找不到该名称的任何较小价格的行。

由于您希望同时选择最大值和最小值，因此需要将这两个表JOINed在一起。

解决方案

这意味着product与自身的3个自联接，其中两个product表用于计算最大价格/日期，另外两个表用于计算最小

在下面的查询中，除最后一行外的所有行都将生成一个具有最大价格&其日期和最小价格&日期。最后一个强制执行您的"最大价格发生在最小价格之后"条件。

p,p2表用于计算最大价格，p3,p4表用于计算最小价格。如果仅将p3加入到p ON p.NAME=p3.NAME中，但额外条件p3.price<=p.price会修剪出我们不感兴趣的行（在价格大于最大值的行上寻找最小价格是没有意义的！），那么它仍然有效。

-- select max price & date of max & min price & date of min:
SELECT p.NAME, p.ENTRY_DATE as dateofMax, p.PRICE as maxPrice, 
       p3.ENTRY_DATE as dateofMin, p3.PRICE as minPrice,
       p.PRICE-p3.PRICE as PriceDiff
FROM products p
LEFT JOIN products p2
 ON p.NAME=p2.NAME AND p.PRICE<p2.PRICE
LEFT JOIN products p3
 ON p.NAME=p3.NAME AND p3.price<=p.price
LEFT JOIN products p4
 ON p.NAME=p4.NAME AND p3.price>p4.price
WHERE p2.PRICE IS NULL
AND p4.PRICE IS NULL
AND p.ENTRY_DATE>p3.ENTRY_DATE; -- make sure dateOfMax>dateofMin

产生

+--------+---------------------+----------+---------------------+----------+-----------+
| NAME   | dateofMax           | maxPrice | dateofMin           | minPrice | PriceDiff |
+--------+---------------------+----------+---------------------+----------+-----------+
| MONKEY | 2012-01-23 16:09:35 |    99.33 | 2012-01-23 09:31:19 |    93.05 | 6.28      |
+--------+---------------------+----------+---------------------+----------+-----------+