MySQL选择MAX(日期时间)不返回最大值 [英] MySQL select MAX(datetime) not returning max value

问题描述

示例表:

id   computer  app      version     build    date
---|---------|------|------------|-------|---------
1  |  aaaa1  | app1 |   1.0.0    |   1   | 2013-11-11 09:51:07
2  |  aaaa1  | app2 |   2.0.0    |   2   | 2013-11-12 09:51:07
5  |  xxxx2  | app1 |   1.0.0    |   1   | 2013-11-13 09:51:07
3  |  cccc3  | app2 |   3.1.0    |   1   | 2013-11-14 09:51:07
4  |  xxxx2  | app1 |   1.0.0    |   2   | 2013-11-15 09:51:07
5  |  cccc3  | app2 |   3.1.1    |   3   | 2013-11-16 09:51:07
6  |  xxxx2  | app1 |   1.0.2    |   1   | 2013-11-17 09:51:07
7  |  aaaa1  | app1 |   1.0.2    |   3   | 2013-11-18 09:51:07

所需的输出(不是确切的格式或上市顺序)，为每台计算机上的每个应用获取最新安装:

Desired output (not exact format or listing order), getting latest install for each app on each computer:

7. aaaa1 - app1 - 1.0.2 - 3 - 2013-11-18 09:51:07
2. aaaa1 - app2 - 2.0.0 - 2 - 2013-11-12 09:51:07
6. xxxx2 - app1 - 1.0.2 - 1 - 2013-11-17 09:51:07
5. cccc3 - app2 - 3.1.1 - 3 - 2013-11-16 09:51:07

我的SQL语句:

SELECT 
        id,
        computer, 
        app, 
        version, 
        build, 
        MAX(date) AS installed
    FROM 
        data 
    WHERE 
        placement = 'xxx'
    GROUP BY 
        app, computer
    ;

这给了我

1. aaaa1 - app1 - 1.0.0 - 1 - 2013-11-11 09:51:07

而不是

7. aaaa1 - app1 - 1.0.2 - 3 - 2013-11-18 09:51:07

如我所料.

MAX(日期)有效.但是后来我没有任何数据可以使用(仅是最新日期).

MAX(date) works if I ONLY select MAX(date) and nothing else. But then I don't get any data to work with (just latest date).

SELECT 
        MAX(date) AS installed

我不是SQL忍者，所以我很快就会因此而挠头.

I'm not an SQL ninja so I will soon go bald by scratching my head because of this.

推荐答案

尝试如下:

SELECT d.id, d.computer, d.app, d.version, d.build, a.installed
FROM data d
INNER JOIN (
  SELECT computer, app, max(DATE) AS installed
  FROM data
  GROUP BY computer, app
  ) a ON a.computer = d.computer AND a.app = d.app
WHERE placement = 'xxx'

内部查询为您提供每对计算机和应用程序的最大日期，然后您只需与之结合即可获取其余信息.

The inner query is getting you the max(date) for each pair of computer and app, then you just join with that to get the rest of the information.

这篇关于MySQL选择MAX(日期时间)不返回最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！