1 select c_id,l_name from list where l_id in (select max(l_id) from list group by c_id);

上面的语句执行超慢.意思是说.以c_id为分组,找出l_id数值最大,所对应的l_name

优化为：

2 select list.c_id,list.l_name from list,(select max(l_id) as max_id from list group by c_id) as temp_table where max_id=l_id;

3 select a.c_id,a.l_name from list as a left join list as b on a.c_id=b.c_id and a.l_id<b.l_id where b.l_id is NULL;

测试结果：


1.第二个1s就完成

2.第三个left join比较慢,需要10s

3.我的那种子查询是最慢的.我看1m都没执行完,怪不得书上都说,建议不要使用子查询.要用连接查询替换.