MySQL选择最大日期和最大时间 [英] MySQL Select Where max date and max time
本文介绍了MySQL选择最大日期和最大时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这种桌子:
CREATE TABLE Buckets (
id INT UNSIGNED AUTO_INCREMENT,
checkDate DATE NOT NULL,
checkTime TIME NOT NULL,
accountId CHAR(13),
costCenter VARCHAR(30),
percentage DECIMAL(5,2),
UNIQUE INDEX (checkDate, checkTime, accountId, costCenter),
PRIMARY KEY (id)
)ENGINE=InnoDB;
我目前有这类数据:
1 2014-03-24 08:11:27 387909559196 72350 86.92
2 2014-03-24 08:11:27 387909559196 analytics 12.71
3 2014-03-24 08:11:27 387909559196 json-files 0.36
4 2014-03-24 08:11:27 387909559196 cloud 0.01
5 2014-03-25 08:11:27 387909559196 72350 86.92
6 2014-03-25 08:11:27 387909559196 analytics 12.71
7 2014-03-25 08:11:27 387909559196 json-files 0.36
8 2014-03-25 08:11:27 387909559196 cloud 0.01
9 2014-03-25 08:38:55 387909559196 72350 86.92
10 2014-03-25 08:38:55 387909559196 analytics 12.71
11 2014-03-25 08:38:55 387909559196 json-files 0.36
12 2014-03-25 08:38:55 387909559196 cloud 0.01
我想选择Select(选择),它选择最大日期,然后选择最大时间.在这种情况下,结果应仅包含9-12行.
I would like to make Select which selects max date and then max time. In this case results should include only rows 9-12.
到目前为止,我已经尝试过这种查询,但是不幸的是我没有得到想要的结果:
So far I have tried this kind of query, but unfortunately I'm not getting the results what I want:
SELECT b.id, b.checkDate, b.checkTime, b.AccountId, b.costCenter, b.percentage, c.id, c.name FROM Buckets b
JOIN CustomerAccounts ca ON (b.accountId= ca.linkedAccountId)
JOIN Customer c ON (ca.customerId = c.id)
WHERE checkDate = (SELECT max(checkDate) FROM Buckets)
AND checkTime = (SELECT max(checkTime) FROM Buckets)
推荐答案
尝试这样
SELECT * FROM
(
SELECT b.id, b.checkDate, b.checkTime, b.AccountId, b.costCenter, b.percentage, c.id, c.name
FROM Buckets b JOIN CustomerAccounts ca ON (b.accountId= ca.linkedAccountId)
JOIN Customer c ON (ca.customerId = c.id)
) AS S JOIN
(
SELECT max(checkDate) AS MaxDate,max(checkTime) As MaxTime FROM Buckets
) AS T ON T.MaxDate = S.checkDate AND S.checkTime = T.MaxTime
这篇关于MySQL选择最大日期和最大时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文