表结构如下<table>
<tr><th>id</th><th>client_id</th><th>info</th><th>addtime</th></tr>
<tr><td>1</td><td>1</td><th>message1</td><th>2012-01-01 00:00:00</td></tr>
<tr><td>2</td><td>2</td><th>message2</td><th>2012-01-01 00:00:02</td></tr>
<tr><td>3</td><td>3</td><th>message22</td><th>2012-01-01 00:00:03</td></tr>
<tr><td>4</td><td>4</td><th>message32</td><th>2012-01-01 00:00:08</td></tr>
<tr><td>5</td><td>1</td><th>message4442</td><th>2012-01-01 00:00:10</td></tr>
<tr><td>6</td><td>2</td><th>message55</td><th>2012-01-01 00:00:19</td></tr>
<tr><td>7</td><td>3</td><th>message621</td><th>2012-01-01 00:00:30</td></tr>
<tr><td>8</td><td>4</td><th>message8o98</td><th>2012-01-01 00:00:55</td></tr>
</table>按client_id分组,查询每组里的最新一条
<tr><th>id</th><th>client_id</th><th>info</th><th>addtime</th></tr>
<tr><td>1</td><td>1</td><th>message1</td><th>2012-01-01 00:00:00</td></tr>
<tr><td>2</td><td>2</td><th>message2</td><th>2012-01-01 00:00:02</td></tr>
<tr><td>3</td><td>3</td><th>message22</td><th>2012-01-01 00:00:03</td></tr>
<tr><td>4</td><td>4</td><th>message32</td><th>2012-01-01 00:00:08</td></tr>
<tr><td>5</td><td>1</td><th>message4442</td><th>2012-01-01 00:00:10</td></tr>
<tr><td>6</td><td>2</td><th>message55</td><th>2012-01-01 00:00:19</td></tr>
<tr><td>7</td><td>3</td><th>message621</td><th>2012-01-01 00:00:30</td></tr>
<tr><td>8</td><td>4</td><th>message8o98</td><th>2012-01-01 00:00:55</td></tr>
</table>按client_id分组,查询每组里的最新一条
解决方案 »
- SQL中如何删除一个table
- 一台机子启用多个mysql ,Fatal error: Please read "Security" section of the manual to find
- 求access表中存储的日期格式转换为mysql整形数据的方法
- 导出sql数据到后缀为.xls文件中
- 写了一个MYSQL的行转列的存储过程。各位给找出不足的地方和更高的效率的方法
- group by 的缺点
- 一条sql 语句!
- mysql获取时间的问题
- 百分请教用php对mysql进行模糊查询问题!!
- 请问局域网访问用export出错该如何处理
- 以前安装过Mysql,后来没用删除了,今天重新安装出现了问题
- 使用mysqli连接,只可以查询,不可以更新和插入
id client_id info addtime
1 1 message1 2012-01-01 00:00:00
2 2 message2 2012-01-01 00:00:02
3 3 message22 2012-01-01 00:00:03
4 4 message32 2012-01-01 00:00:08
5 1 message4442 2012-01-01 00:00:10
6 2 message55 2012-01-01 00:00:19
7 3 message621 2012-01-01 00:00:30
8 4 message8o98 2012-01-01 00:00:55
and cdate(a.info & a.addtime)<cdate(info & addtime)
)
[征集]分组取最大N条记录方法征集,及散分....
方案1:SELECT * FROM (SELECT * FROM client_status ORDER BY addtime DESC )a GROUP BY client_id
explain分析id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
2 DERIVED client_status ALL NULL NULL NULL NULL 9 Using filesort
方案2:SELECT * FROM client_status a WHERE NOT EXISTS (SELECT 1 FROM lient_status WHERE a.client_id = client_id AND a.addtime < addtime)explain分析id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 9 Using where
2 DEPENDENT SUBQUERY client_status ref client_id client_id 3 pdmon.a.client_id 1 Using where
方案3:SELECT * FROM `client_status` WHERE addtime IN (SELECT max( addtime ) FROM client_status GROUP BY client_id )explain分析id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY client_status ALL NULL NULL NULL NULL 9 Using where
2 DEPENDENT SUBQUERY client_status index NULL client_id 3 NULL 1 如果看不清,可以移步这里:http://www.cnblogs.com/mybest/archive/2012/03/26/2418200.html
如果有更高效的SQL,请继续跟贴
select * from table_name group by client_id order by addtime desc