mysql - 如何在该日期内选择日期和时间？

Question

我读了几篇关于这个的文章:Select max date, then max time这似乎最有帮助，但我看不到实现它的方法。

有五张 table 。我加入他们。我只需要从第一个表中选择日期和时间最长的一行，从第二个表中选择相同的行，然后将其余的加入其他值。使用我编写的代码，我得到多行。看来时间选择不对。

可以用子查询中的子查询来完成。我试过这样的事情:

    SELECT * from table1 
    INNER JOIN table2 ON table1.date = table2.date AND table1.gm = table2.gm        
    INNER JOIN table3 ON table2.gm = table3.gm        ...
    WHERE table3.date = :date AND table4.date = :date ...
    AND table1.date IN(
        SELECT MAX(table1.date) FROM table1 WHERE table1.time IN(
            SELECT MAX(table1.time) FROM table1
    )
    )
    AND table2.date IN(
        SELECT MAX(table2.date) FROM table1 WHERE table2.time IN(
            SELECT MAX(table2.time) FROM table2        )
    )
    ORDER BY table1.id

问题是:如何在加入所有这些日期最高且该日期时间最高的位置后获得单行？谢谢!

编辑:我对此感到抱歉。我忘了说我需要与表(gm 列)中的特定值相关的最大日期的最大时间。所以这是一行(在示例中我给出的是 table1.gm 和 table2.gm ...)，每个 .gm 值中的每一个在每个表中都是相同的，而不仅仅是一行。 Nick 和 Salim 提供的解决方案有效，但我没有解决问题。

编辑:已解决!在实现 Nick 的解决方案后，我只需要添加 GROUP BY cntrs_reper.gm_company_no，cntrs_reper.date。就是这样。对于一个表中的每一行都具有来自其他人的最高日期和时间!!感谢大家。

编辑。如果这可以帮助这是完整的查询:

SELECT  cntrs_gm.gm_company_no                          AS      company_c_g,  
 bns_gms.ded_bns                                    AS      ded_bns_gms,  
     bns_gms.no_ded_bns                             AS      no_ded_bns_gms,  
     bns_gms.wag_ded_bns                            AS      wag_ded_bns_gms,  
     cntrs_gm.cur_credit                            AS      cur_credit_c_g,  
     cntrs_gm.cdrop                                 AS      cdrop_c_g,  
     cntrs_gm.total_jp                              AS      total_jp_c_g,  
     cntrs_gm.games                                 AS      games_c_g,  
     cntrs_gm.wgames                                AS      wgames_c_g,  
     cntrs_gm.doors                                 AS      doors_c_g,  
     cntrs_gm.power                                 AS      power_c_g,  
     cntrs_gm.total_in                            AS        total_in_c_g,  
     cntrs_gm.total_out                           AS        total_out_c_g,  
     cntrs_gm.total_acc                           AS        total_acc_c_g,  
     cntrs_gm.total_bet                           AS        total_bet_c_g,  
     cntrs_gm.total_win                           AS        total_win_c_g,  
     cntrs_gm.total_bonus                         AS        total_bonus_c_g,  
     cntrs_gm.date              AS      date_c_g,  
     cntrs_reper.gm_company_no                    AS        company_reper,  
 bns_reper.ded_bns                            AS        ded_bns_reper,  
     bns_reper.no_ded_bns                         AS        no_ded_bns_reper,  
     bns_reper.wag_ded_bns          AS      wag_ded_bns_reper,  
     cntrs_reper.cur_credit         AS      cur_credit_reper,  
     cntrs_reper.cdrop              AS      cdrop_reper,  
     cntrs_reper.total_jp                         AS        total_jp_reper,  
     cntrs_reper.games              AS      games_reper,  
     cntrs_reper.wgames             AS      wgames_reper,  
     cntrs_reper.doors              AS      doors_reper,  
     cntrs_reper.power              AS      power_reper,  
     cntrs_reper.total_in                         AS        total_in_reper,  
     cntrs_reper.total_out          AS      total_out_reper,  
     cntrs_reper.total_acc          AS      total_acc_reper,  
     cntrs_reper.total_bet          AS      total_bet_reper,  
     cntrs_reper.total_win          AS      total_win_reper,  
     cntrs_reper.total_bonus            AS      total_bonus_reper,  
     cntrs_reper.date                             AS        date_reper,  
     cntrs_reper.time               AS      time_reper,
     bns_reper.time                 AS      time_c_g,
     gms_cfg.gm_no                                    AS              machine_id,  
     gms_cfg.denom_cin                        AS              machine_cin  
     FROM bns_gms  
     INNER JOIN cntrs_gm  
     ON bns_gms.gm_company_no = cntrs_gm.gm_company_no  AND bns_gms.date = cntrs_gm.date
     INNER JOIN bns_reper 
     ON cntrs_gm.gm_company_no = bns_reper.gm_company_no  
     INNER JOIN cntrs_reper  
     ON bns_reper.gm_company_no = cntrs_reper.gm_company_no  AND bns_reper.date = cntrs_reper.date
     INNER JOIN gms_cfg  
     ON cntrs_reper.gm_company_no =  gms_cfg.gm_no  
     WHERE   bns_reper.date IN(
        SELECT MAX(DATE(bns_reper.date)) FROM bns_reper WHERE bns_reper.time IN(
            SELECT MAX(TIME(bns_reper.time)) FROM bns_reper
        )
        )
     AND cntrs_reper.date IN(
        SELECT MAX(DATE(cntrs_reper.date)) FROM cntrs_reper  WHERE cntrs_reper.time IN(
            SELECT MAX(TIME(cntrs_reper.time)) FROM cntrs_reper
        )
        )

     ORDER BY cntrs_gm.gm_company_no

数据库示例

bns_gms

bns_reper

cntrs_gm

cntrs_reper

gms_cfg

Answer 1

您当前查询的问题是它将选择所有行，其中 table1.date 是出现最高时间的最新日期，可能不止一个，例如对于诸如

之类的数据

id  date          time
1   2018-03-30    18:40
2   2018-03-31    12:20
3   2018-03-31    19:20

您的WHERE 子句:

table1.date IN(
    SELECT MAX(table1.date) FROM table1 WHERE table1.time IN(
        SELECT MAX(table1.time) FROM table1
)

将选择 id=2 和 id=3 的行，因为它们都有 date = '2018-03-31'当最大时间出现时。

你想要做的是选择最晚日期的行，你可以使用它

table1.date = (SELECT MAX(date) FROM table1) AND
table1.time = (SELECT MAX(time) FROM table1 WHERE date = (SELECT(MAX(date) FROM table1))

通过使用别名，可以简化(因为我们已经知道 table1.date = MAX(date) FROM table1)

table1.date = (SELECT MAX(date) FROM table1) AND
table1.time = (SELECT MAX(time) FROM table1 AS t1 WHERE t1.date = table1.date)