mysql - 使MySQL查询通过group by语句选择最大时间戳

这是我的问题：

SELECT
    log_timestamp,
    system_name,
    faction_name,
    faction_allegiance,
    faction_government_type,
    current_state,
    pending_state,
    recovering_state,
    influence AS influence,
    transactions.transaction_id
FROM `bgs`.`faction_log`
    INNER JOIN `factions` ON `factions`.`faction_id` = `faction_log`.`faction_id`
    INNER JOIN `transactions` ON `transactions`.`transaction_id` = `faction_log`.`transaction_id`
    INNER JOIN `system_log` ON `system_log`.`transaction_id` = `transactions`.`transaction_id`
    INNER JOIN `systems` ON `systems`.`system_id` = `transactions`.`associated_system_id`
WHERE
    log_timestamp BETWEEN bgs.find_closest_tick_time(date('2017-08-14')) AND bgs.find_closest_tick_time(DATE_ADD(date('2017-08-14'), INTERVAL 1 DAY)) AND
    associated_system_id = 4
GROUP BY
    faction_name,
    log_timestamp,
    system_name,
    faction_allegiance,
    faction_government_type,
    current_state,
    pending_state,
    recovering_state,
    influence,
    transactions.transaction_id

它工作得很好，现在看起来像这样：
+---------------------+-------------+-----------------+| log_timestamp | system_name | faction_name |+---------------------+-------------+-----------------+| 2017-08-14 11:18:46 | Nagii | Nagii Autocracy || 2017-08-14 12:38:03 | Nagii | Nagii Autocracy || 2017-08-14 13:40:18 | Nagii | Nagii Autocracy || 2017-08-15 06:09:52 | Nagii | Nagii Autocracy || 2017-08-14 11:18:46 | Nagii | Nagii Network || 2017-08-14 12:38:03 | Nagii | Nagii Network || 2017-08-14 13:40:18 | Nagii | Nagii Network || 2017-08-15 06:09:52 | Nagii | Nagii Network || 2017-08-14 11:18:46 | Nagii | Nagii Posse || 2017-08-14 12:38:03 | Nagii | Nagii Posse || 2017-08-14 13:40:18 | Nagii | Nagii Posse || 2017-08-15 06:09:52 | Nagii | Nagii Posse || 2017-08-14 11:18:46 | Nagii | Nagii Union || 2017-08-14 12:38:03 | Nagii | Nagii Union || 2017-08-14 13:40:18 | Nagii | Nagii Union || 2017-08-15 06:09:52 | Nagii | Nagii Union || 2017-08-14 11:18:46 | Nagii | Nagii United Co || 2017-08-14 12:38:03 | Nagii | Nagii United Co || 2017-08-14 13:40:18 | Nagii | Nagii United Co || 2017-08-15 06:09:52 | Nagii | Nagii United Co |+---------------------+-------------+-----------------+20 rows in set (0.00 sec)
但我只想选择包含字段LogiTimeType最大值的行。（在本例中为2017-08-15 06:09:52）。我试着用这样的东西：
HAVING transactions.log_timestamp >= max(date('2017-08-15 06:09:52'));
但不是这样的：
HAVING transactions.log_timestamp >= max(log_timestamp);
我做错什么了？

最佳答案

这是另一个解决方案。如果只希望在选择列表的所有列中每个不同的值有一行，则不需要“分组依据”。您可以对SELECT DISTINCT执行相同的操作，如下所示：

SELECT DISTINCT
    log_timestamp,
    system_name,
    faction_name,
    faction_allegiance,
    faction_government_type,
    current_state,
    pending_state,
    recovering_state,
    influence AS influence,
    transactions.transaction_id
FROM `bgs`.`faction_log`
    INNER JOIN `factions` ON `factions`.`faction_id` = `faction_log`.`faction_id`
    INNER JOIN `transactions` ON `transactions`.`transaction_id` = `faction_log`.`transaction_id`
    INNER JOIN `system_log` ON `system_log`.`transaction_id` = `transactions`.`transaction_id`
    INNER JOIN `systems` ON `systems`.`system_id` = `transactions`.`associated_system_id`
WHERE
    log_timestamp BETWEEN bgs.find_closest_tick_time(date('2017-08-14')) AND bgs.find_closest_tick_time(DATE_ADD(date('2017-08-14'), INTERVAL 1 DAY)) AND
    associated_system_id = 4
ORDER BY log_timestamp DESC
LIMIT 1

因为您只需要具有max log_timestamp的行，所以可以按该列降序排序，并使用LIMIT只返回结果中的第一行。