我有一个mysql表,其中包含所有用户订阅的计划。我试图创建一个SELECT语句,允许我从表中选择一个特定的用户。此外,如果该用户有重复条目,它将获得所有条目的最新日期。
例如,我想从表中查找John,我应该得到的日期是2015-09-10。
Subscribed table
ID FirstName Date
-------------------------------
1 John 2015-05-30
2 Mary 2014-01-10
3 John 2015-09-10
4 John 2015-03-15
5 Loen 2013-12-11
我应该如何去创建SELECT语句上面的结果?
如果您只需要第一个,您可以将LIMIT
与ORDER BY
结合使用:
SELECT * FROM `Subscribed`
WHERE `FirstName` = 'John' -- Get the name John.
ORDER BY `Date` DESC -- Sort results in descending order by Date.
LIMIT 1 -- Limit the results to one.
结果将按日期降序排序,LIMIT 1
给出第一行。
或者更准确地说,如果需要多行,也可以使用Grouping Functions。
SELECT `FirstName`, MAX(`Date`) FROM `Subscribed`
WHERE `FirstName` = 'John'
GROUP BY `FirstName`
小提琴:http://www.sqlfiddle.com/# !9/ebb90/1
试试这个
SELECT FirstName, max(Date) FROM yourTable
WHERE FirstName = 'John'
您还可以使用group by
来获取所有人的结果,而不仅仅是John
如果您想为所有用户这样做:
select s.*
from subscribed s join
(select firstname, max(date) as maxd
from subscribed
group by firstname
) ss
on ss.firstname = s.firstname and ss.maxd = s.date;
查询所需输出:
SELECT * FROM (SELECT * FROM `Subscribed`
ORDER BY `DATE` DESC) t group by firstName
也可以使用MAX和GROUP BY
SELECT firstName, MAX(date) FROM `Subscribed`
group by firstName
验证这里的输出:http://www.sqlfiddle.com/#!9/ec5d1/1
输出:id firstName date
3 John September, 10 2015 00:00:00
2 Mary January, 10 2014 00:00:00
Gordeon Linoff有一个很好的答案,如果你的ID是一个主键,并且总是按时增长,你可以这样做,以获得更好的速度:
SELECT s.*
FROM subscribed s,
(SELECT FirstName, max(ID) maxID FROM subscribed GROUP BY 1) AS ss
WHERE s.ID = ss.maxID;