SQL分组后取最新值

这个还是有点没明白,这里先简单记录下。

注意,MySql如果不加索引,LEFT JOIN有点慢。

需求:对x_id分组,取最新的一个值。

MySql的写法,利用子查询先排序,然后GROUP BY后查询默认会取出每组第一条数据。

SELECT * FROM (SELECT db_1.id,db_1.r_id,db_1.x_id AS seat_id,db_1.time AS time,db_1.p_state, db_2.name AS name FROM db_1 LEFT JOIN db_2 ON db_1.match_id=db_2.match_id WHERE x_id <> ‘0000000000000000’ AND r_id =‘1’ ORDER BY db_1.id DESC) AS t GROUP BY t.seat_id

SQL Server下面的也能用,但是我么看懂。

SELECT A.r_id,A.x_id AS seat_id,A.time AS time,A.p_state, db_2.name AS name FROM db_1 AS A LEFT JOIN db_2 ON A.match_id=db_2.match_id WHERE 1>(SELECT COUNT(1) FROM db_1 AS B WHERE A.x_id=B.x_id AND A.ID<B.ID) AND x_id <> ‘0000000000000000’ AND r_id =‘1’ ORDER BY A.ID DESC

 

下面收集了一些资料,供参考:

MySQL 先排序再分组的问题 http://wenku.baidu.com/link?url=PsAGxhJY5ycK4Jk92crVEa-D60blwBiWFsY1ELrKkJxJzNW1XG-XAOdC7hFBW4NIN5ptB2oNlNQow1sWgPwwfF3aw8C0Wuq6-z3uaYfgc3y
mysql分组取每组前几条记录(排序) http://blog.sina.com.cn/s/blog_4c197d420101e408.html
left join on 和where条件的放置 http://blog.csdn.net/muxiaoshan/article/details/7617533
mysql 分组后取每个组内最新的一条数据(组内排序以及limit)http://www.dewen.io/q/12364/
How to select the first/least/max row per group in SQL http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

发表评论

电子邮件地址不会被公开。 必填项已用*标注