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');
我试图根据
id
和posted_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/