php - MySQL 获取最小值、最大值和最旧的分组依据

Question

我有一个正在处理的小项目，并具有以下设置:

MariaDB [b7_19195200_prices]> select * from watchlist;
+----+-------------+-------+---------------------+--------+
| id | item        | price | recorded            | errors |
+----+-------------+-------+---------------------+--------+
|  3 | 32725999014 | 1.46  | 2016-11-21 20:30:22 |      0 |
|  4 | 32725999014 | 1.93  | 2016-11-21 20:56:21 |      0 |
|  5 | 32725999014 | 2.01  | 2016-11-21 20:56:32 |      0 |
|  6 |      122444 | 22.43 | 2016-11-21 21:03:19 |      0 |
|  7 | 32725999014 | 1.11  | 2016-11-21 21:25:33 |      0 |
+----+-------------+-------+---------------------+--------+
5 rows in set (0.00 sec)

所以我想获得最高价格、最低价格和第一个(该商品价格的最旧记录)。所以我会得到如下结果:

+----+-------+-------+-------------+----------+
| id | low   | high  | item        | original | 
+----+-------+-------+-------------+----------+
|  6 | 22.43 | 22.43 |      122444 |    12.11 |
|  3 | 1.11  | 2.01  | 32725999014 |    0.51  |
+----+-------+-------+-------------+----------+

其中 low、high 和 original 分别是商品的最低价、最高价和原始价。一个好处是能够知道什么时候最高和最低。我一直在使用 2 个不同的查询来获取最高价、最低价和原始价格:

SELECT `id`, MIN(`price`) low, MAX(`price`) high, `item`
FROM `watchlist`
GROUP BY `item`;

和

SELECT `id`, MIN(`recorded`), `price` original, `item`
FROM `watchlist`
GROUP BY item;

我能做到吗？

谢谢!

Answer 1

如果你不需要id你可以使用join

 select t1.*, t2.* 
 from ( 
  SELECT MIN(`price`) low, MAX(`price`) high, `item`
  FROM `watchlist`
  GROUP BY `item`
) t1
left  join (
  SELECT  MIN(`recorded`), `price` original, `item`
  FROM `watchlist`
  GROUP BY item
) t2 on t1.`item` =  t2.`item`

否则，如果您还需要子选择上的 id 连接和元组

 select t1.*, t2.* 
 from ( 
  SELECT MIN(`price`) low, MAX(`price`) high, `item`
  FROM `watchlist`
  GROUP BY `item`
)  t1
left  join (
  select `id`, `recorded`,  `price`, `item`
  from `watchlist`
  where (  `item`,  `recorded`) in (
        select `item`,  min(`recorded`)
        FROM `watchlist`
        GROUP BY item
  )
) t2 on t1.`item` =  t2.`item`

第二个应该是正确的，因为明确选择行并获得非聚合值的第一个结果