主页 / dba / 问题 / 315841
Accepted
Sahasrar
Asked: 2022-08-20 10:43:37 +0800 CST

如何编写查询以选择同一日期的最大值

  • 772

请帮我为 MySQL 编写这个查询。我想从同一日期时间中选择最高值。因此，如果有多个相等的日期时间值，则只返回价格最高的一行。总而言之，每个时间戳一行，价格最高。

+------+---------------+--------------+---------+---------+---------------------+
|  id  | source_curr   | dest_curr    |  price  | act     |   timestamp         |
+------+---------------+--------------+---------+---------+---------------------+
|  164 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  769 | EUR           | USD          | 0.99125 | bids    | 2022-07-18 20:52:38 |
| 1370 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  568 | EUR           | USD          | 0.99322 | bids    | 2022-07-18 20:52:38 |
| 1572 | EUR           | USD          | 0.96335 | bids    | 2022-07-18 20:52:38 |
| 1771 | EUR           | USD          | 0.99432 | bids    | 2022-07-18 20:52:38 |
|  971 | EUR           | USD          | 0.99535 | bids    | 2022-07-18 20:52:38 |
|  367 | EUR           | USD          | 0.99334 | bids    | 2022-07-18 20:52:38 |
| 1173 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  574 | EUR           | USD          |  0.9865 | bids    | 2022-08-11 15:33:22 |
| 1179 | EUR           | USD          |  0.9845 | bids    | 2022-08-11 15:33:22 |
|  373 | EUR           | USD          |  0.9863 | bids    | 2022-08-11 15:33:22 |
| 1578 | EUR           | USD          |  0.9875 | bids    | 2022-08-11 15:33:22 |
|  977 | EUR           | USD          |  0.9868 | bids    | 2022-08-11 15:33:22 |
| 1376 | EUR           | USD          |  0.9864 | bids    | 2022-08-11 15:33:22 |
| 1777 | EUR           | USD          |  0.9365 | bids    | 2022-08-11 15:33:22 |
|  170 | EUR           | USD          |  0.9165 | bids    | 2022-08-11 15:33:22 |
|  775 | EUR           | USD          |  0.9865 | bids    | 2022-08-11 15:33:22 |
|  773 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1775 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1576 | EUR           | USD          |    0.98 | bids    | 2022-08-11 17:50:31 |
|  572 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
|  168 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
|  371 | EUR           | USD          |    0.95 | bids    | 2022-08-11 17:50:31 |
| 1177 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1374 | EUR           | USD          |    0.91 | bids    | 2022-08-11 17:50:31 |
|  975 | EUR           | USD          |    0.96 | bids    | 2022-08-11 17:50:31 |
|  581 | EUR           | USD          | 0.98465 | bids    | 2022-08-11 21:10:27 |
| 1784 | EUR           | USD          | 0.98564 | bids    | 2022-08-11 21:10:27 |
|  984 | EUR           | USD          | 0.98123 | bids    | 2022-08-11 21:10:27 |
|  782 | EUR           | USD          | 0.98132 | bids    | 2022-08-11 21:10:27 |
| 1383 | EUR           | USD          | 0.98115 | bids    | 2022-08-11 21:10:27 |
| 1585 | EUR           | USD          | 0.98848 | bids    | 2022-08-11 21:10:27 |
| 1186 | EUR           | USD          | 0.98185 | bids    | 2022-08-11 21:10:27 |
|  380 | EUR           | USD          | 0.98184 | bids    | 2022-08-11 21:10:27 |
|  177 | EUR           | USD          | 0.98148 | bids    | 2022-08-11 21:10:27 |
+------+---------------+--------------+---------+---------+---------------------+
mysql select

2 个回答

  1. With CTE AS (
Select *,(row_number() over(partition by timestamp order by price Desc)) as rn
From table
)

SELECT * FROM CTE 
WHERE rn = 1;

    此查询将帮助您为每个时间戳获取 1 行的最高价格。

    • 1
  2. Best Answer

    对于较旧的 MySQL 版本，从同一日期时间选择最高值的一种可能方法是在连接条件上使用每个日期时间的最大价格的子查询。

    select max(tt.id) as id,
       tt.source_curr,
       tt.dest_curr,
       tt.price,
       tt.act,
       tt.`timestamp`
from test_tbl tt
inner join (select `timestamp`,max(price) as price
            from test_tbl
            group by  `timestamp`
            ) as max_prc on tt.`timestamp`=max_prc.`timestamp` and tt.price=max_prc.price
group by source_curr,dest_curr,price,act,`timestamp` ;

    子查询选择按 . 分组的最高价格timestamp。

    因为价格不是唯一的（相同的最高价格对于相同的时间戳重复多次），所以我选择了 max id，然后按其他列分组，每个时间戳只得到一个结果。

    如果您有 MySQL 8+ ，则可以使用 row_number 作为提到的其他答案。

    select id,source_curr,dest_curr,price,act,timestamp
from ( Select *,
             row_number() over(partition by timestamp order by price desc,id desc ) as rn
      from test_tbl 
     ) as tbl
where rn=1;

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ab5dd84bdb6f524c33e88b13c5c98450

    注意。请避免在您的情况下使用关键字和保留字，例如时间戳。

    • 1

相关问题