表结构
userlog
id | int |
userid | int |
logtime | timestamp |
一、按userid分组 查询每个用户最新一条登录记录
1.
select a.* from userlog a,(select userid,max(logtime) logtime from userlog group by userid) b where a.userid = b.userid and a.logtime = b.logtime;
2.
select a.* from userlog a INNER JOIN(select userid,max(logtime) logtime from userlog group by userid) b ON a.userid = b.userid and a.logtime = b.logtime;
1和2在数据较少的情况下效率近似,数据量较大时建议使用2方式。
3.
select a.* from userlog a where not exists(select 1 from userlog where userid = a.userid and logtime > a.logtime);
4.
select a.* from userlog a where logtime = (select max(logtime) from userlog where userid = a.userid);
5.
select a.* from userlog a where 1 > (select count(*) from userlog where userid = a.userid and logtime > a.logtime );
1,2,3,4,5都属于带子查询的sql,其中1和2 是 关联子查询,先根据子查询查询出临时表,再根据表连接查询出对应的结果
3,4,5属于嵌套子查询 先在外部查询中取得一条记录的userid和logtime,然后执行子查询,条件就是子查询中的userid的值与外部刚才取到的值一样的所有记录的logtime字段的最大值,
也就是说对于外部的每一条记录都需要通过一次子查询,这严重影响了查询的效率
更多其他详细的查询 http://www.cnblogs.com/netserver/p/4518995.html