MySQL MySQL选择MAX（日期时间）不返回最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20049997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select MAX(datetime) not returning max value
提问by DannyThunder
Example table:
示例表：
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
My SQL statement:
我的 SQL 语句：
SELECT
id,
computer,
app,
version,
build,
MAX(date) AS installed
FROM
data
WHERE
placement = 'xxx'
GROUP BY
app, computer
;
This gives me:
这给了我：
1. aaaa1 - app1 - 1.0.0 - 1 - 2013-11-11 09:51:07
and not
并不是
7. aaaa1 - app1 - 1.0.2 - 3 - 2013-11-18 09:51:07
as I expected.
正如我所料。
MAX(date) works if I ONLYselect MAX(date) and nothing else. But then I don't get any data to work with (just latest date).
如果我只选择 MAX(date) 而没有其他选择，则 MAX(date) 有效。但后来我没有得到任何数据（只是最新日期）。
SELECT
MAX(date) AS installed
I'm not an SQL ninja so I will soon go bald by scratching my head because of this.
我不是 SQL 忍者，所以我很快就会因为这个挠头而秃顶。
采纳答案by Filipe Silva
Try like 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.
内部查询为您提供每对计算机和应用程序的 max(date)，然后您只需加入该查询即可获取其余信息。
回答by par181
Try by casting the Datetime field
尝试通过投射日期时间字段
SELECT
id,
computer,
app,
version,
build,
MAX(cast(date as Datetime)) AS installed
FROM
data
WHERE
placement = 'xxx'
GROUP BY
app, computer, id, version, build
;
回答by Gravy
Whats wrong with:
有什么问题：
SELECT
id,
computer,
app,
version,
build,
`date` AS installed
FROM
data
WHERE
placement = 'xxx'
ORDER BY installed DESC
GROUP BY app;
回答by Andrius K
MAX did not work for me, what worked was additional subquery where I preordered table by date:
MAX 对我不起作用，有用的是我按日期预排序表的附加子查询：
SELECT d.id, d.computer, d.app, d.version, d.build, a.installed
FROM data d
INNER JOIN (
SELECT computer, app, date as installed
FROM (
SELECT computer, app, date
FROM data
ORDER BY date desc
) as t
GROUP BY computer, app
) a ON a.computer = d.computer AND a.app = d.app
WHERE placement = 'xxx'
回答by artusat0r
It might be because you store your date as String, and comparing string act differently than comparing integer. You should store your date in unix Timestamp format, and they will be much easier to compare. But will need an extra effort to be displayed as normal English date.
这可能是因为您将日期存储为字符串，并且比较字符串的行为与比较整数的行为不同。您应该以unix Timestamp 格式存储您的日期，这样比较它们会容易得多。但是需要额外的努力才能显示为正常的英文日期。
回答by edem. С Днём Победы
max - is an aggregate function try to add all columns from select statement to GROUP BY:
max - 是一个聚合函数，尝试将 select 语句中的所有列添加到GROUP BY：
GROUP BY
app, computer, id, version, build.
