SELECT * FROM discussion_comments GROUP BY disc_id ORDER BY posted_date DESC

我有下面的表格示例:

CREATE TABLE example
(
id int(11),
cname varchar(11),
posted_date date,
posted_time varchar(20)
);


其值如下:

INSERT INTO example
VALUES
(1,'abc','2015-03-26','04:25 PM'),
(1,'def','2015-03-27','04:30 PM'),
(2,'ghi','2015-03-11','02:25 AM'),
(2,'jkl','2015-03-15','12:25 PM');


我试图根据idposted_date字段仅将最新值添加到posted_time的表中。

我想要达到的结果是:

(1,'def','2015-03-27','04:30 PM')
(2,'jkl','2015-03-15','12:25 PM')


我尝试过的查询如下:

SELECT * FROM `example GROUP BY id ORDER BY posted_date DESC


我没有得到理想的结果。我哪里做错了??

最佳答案

有很多方法,一种方法是left join

select e1.* from example e1
left join example e2 on e1.id = e2.id
and e1.posted_date < e2.posted_date where e2.id is null;


Uncorrelated Sub-query

select e1.* from example e1
join (
   select id,max(posted_date) as posted_date from example group by id
)x
on x.id = e1.id and x.posted_date = e1.posted_date ;

关于mysql - 无法根据发布的时间获取最新值添加到Mysql表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29295624/

10-12 04:53