背景:

我正在编写Python脚本以获取一些数据。我在SQL数据库中有2个表engine_hoursmachines。我想获取过去2天收到的最新数据(通过this date_recorded >=完成)。

表格:

第一张表:

engine_hours
=============
machine_id
date_recorded
value


第二张表:

machines
========
id
title


样本数据表:

第一张表:

                    engine_hours
==================================================
 machine_id  |      date_recorded       | value
-------------+--------------------------+---------
     1       |   16/10/2018  20:30:02   |   10
     3       |   16/10/2018  19:02:32   |   42
     2       |   16/10/2018  20:32:56   |   13
     2       |   16/10/2018  19:23:23   |   12
     1       |   16/10/2018  16:54:59   |   10
     1       |   16/10/2018  16:52:59   |   10
     1       |   14/10/2018  10:24:59   |   10


第二张表:

    machines
==================
  id  |   title
------+-----------
   1  |  ABC-123
   2  |  DEF-456
   3  |  GHI-789


所需输出:

=============================================================
 machine_id  |  title  |       date_recorded      | value
     1       | ABC-123 |   16/10/2018  20:30:02   |   10
     2       | DEF-456 |   16/10/2018  20:32:56   |   13
     3       | GHI-789 |   16/10/2018  19:02:32   |   42


我尝试过的

我尝试了4个不同的查询,但失败了:

engine_hours_query = "SELECT ma.`title`, eh.`machine_id`, eh.`value`, eh.`date_recorded` " \
                     "FROM `engine_hours` AS eh inner join `machines` AS ma " \
                     "WHERE eh.`machine_id` IN ({}) AND eh.`date_recorded` >=  \"{}\" " \
                     " AND eh.`machine_id` = ma.`id`".format(", ".join([str(m_id) for m_id in list_of_machine_ids]),
                                                             cut_off_date)

engine_hours_query_2 = "SELECT `machine_id`, `value`, `date_recorded` FROM `engine_hours` AS eh " \
                       "WHERE `date_recorded` = ( SELECT MAX(`date_recorded`) " \
                       "FROM `engine_hours` AS eh2  " \
                       "WHERE eh.`machine_id` = eh2.`machine_id`)"


engine_hours_query_3 = "SELECT `machine_id`, `value`, `date_recorded` FROM `engine_hours` AS eh " \
                       "WHERE `date_recorded` = ( SELECT MAX(`date_recorded`) " \
                       "FROM `engine_hours` AS eh2 ) " \
                       "WHERE eh.`date_recorded` >=  \"{}\"".format(cut_off_date)

engine_hours_query_4 = "SELECT ma.`title`, eh.`machine_id`, eh.`value`, eh.`date_recorded` " \
                     "FROM `engine_hours` AS eh inner join `machines` AS ma " \
                     "WHERE eh.`machine_id` IN ({}) AND eh.`date_recorded` >=  \"{}\" " \
                     " AND eh.`machine_id` = ma.`id`".format(", ".join([str(m_id) for m_id in list_of_machine_ids]),
                                                             cut_off_date)


研究:


How to SELECT by MAX(date)?
Select latest record in table (datetime field)

最佳答案

此版本应满足您的要求:

SELECT eh.machine_id, eh.value, eh.date_recorded
FROM engine_hours eh
WHERE eh.date_recorded = (SELECT MAX(eh2.date_recorded)
                          FROM engine_hours eh2
                          WHERE eh.machine_id = eh2.machine_id
                         );


如果仍然需要该条件,则可能要添加AND eh.date_recorded >= NOW() - INTERVAL 2 DAY

关于mysql - 特定日期后如何选择MAX(日期),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52813176/

10-12 12:51
查看更多