如何在一个mysql查询中找到min，max，first，last？

nfg76nw0 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(280)

我有一张像下面这样的table

products

 id  price  date 
-------------------------------
 1    1     2018-04-27 12:10:15
 2    2     2018-04-27 12:10:15
 3    5     2018-04-27 12:10:18
 4    3     2018-04-27 12:10:18
 5    4     2018-04-27 12:11:25
 6    3     2018-04-27 12:11:25

注：我必须找到每分钟的最低价格，最高价格，起始价格，结束价格。
我的预期产出是

firstPrice lastPrice minPrice maxPrice
---------------------------------------
    1          3         1       5     --> Grouped for (2018-04-27 12:10)
    4          3         3       4     --> Grouped for (2018-04-27 12:11)

我的问题是

SELECT b.lastPrice,c.firstPrice,min(a.price) as minPrice,max(a.price) as maxPrice from products as a left join (select price as lastPrice,date from products order by date desc) as b on a.date = b.date left join (select price as firstPrice,date from products order by date asc) as c on a.date = c.date where a.date >= '2018-04-27 12:10:00'

我不知道该怎么做才能得到预期的结果。

mysql

来源：https://stackoverflow.com/questions/50056997/how-to-find-min-max-first-last-in-one-mysql-query

2条答案

按热度按时间

y0u0uwnf1#

您可以使用以下选项：

SELECT 
    (SELECT price FROM products WHERE DATE_FORMAT(p.`date`, '%Y%m%d%H%i') = DATE_FORMAT(`date`, '%Y%m%d%H%i') ORDER BY `date` ASC, id ASC LIMIT 1) AS firstPrice,
    (SELECT price FROM products WHERE DATE_FORMAT(p.`date`, '%Y%m%d%H%i') = DATE_FORMAT(`date`, '%Y%m%d%H%i') ORDER BY `date` DESC, id DESC LIMIT 1) AS lastPrice,
    MIN(price) AS minPrice, MAX(price) AS maxPrice 
FROM products p 
GROUP BY DATE_FORMAT(`date`, '%Y%m%d%H%i')

demo:http：//sqlfiddle.com/#！9/8a989/15/0号

赞(0）回复(0）举报 2021-06-23

y1aodyip2#

可能在子查询中使用limit子句获取第一个和最后一个价格，并按日期分组。

drop table if exists t;
create table t(id int,  price int , dt datetime);
insert into t values
( 1  ,  1  ,   '2018-04-27 12:10:15'),
( 2  ,  2  ,   '2018-04-27 12:10:15'),
( 3  ,  5  ,   '2018-04-27 12:10:18'),
( 4  ,  3  ,   '2018-04-27 12:10:18'),
( 5  ,  4  ,   '2018-04-27 12:11:25'),
( 6  ,  3  ,   '2018-04-27 12:11:25');

select concat(date(dt),' ',hour(dt), ':',minute(dt) ,':00') Timeslot,
         (select price from t t1 where concat(date(t1.dt),' ',hour(t1.dt), ':',minute(t1.dt) ,':00') = concat(date(t.dt),' ',hour(t.dt), ':',minute(t.dt) ,':00') order by id limit 1) firstprice,
         (select price from t t1 where concat(date(t1.dt),' ',hour(t1.dt), ':',minute(t1.dt) ,':00') = concat(date(t.dt),' ',hour(t.dt), ':',minute(t.dt) ,':00') order by id desc limit 1) lastprice,
         min(price),max(price)
from t
group by concat(date(dt),' ',hour(dt), ':',minute(dt) ,':00');

+---------------------+------------+-----------+------------+------------+
| Timeslot            | firstprice | lastprice | min(price) | max(price) |
+---------------------+------------+-----------+------------+------------+
| 2018-04-27 12:10:00 |          1 |         3 |          1 |          5 |
| 2018-04-27 12:11:00 |          4 |         3 |          3 |          4 |
+---------------------+------------+-----------+------------+------------+
2 rows in set (0.00 sec)

赞(0）回复(0）举报 2021-06-23

我来回答

如何在一个mysql查询中找到min，max，first，last？

2条答案

相关问题

热门标签

最新问答