mysql查询最新记录 海量_MySql 查询最新60条记录

取每个ModuleName+Code组合最新的60条记录

sql1：效率慢，理解简单

select p.ModuleName,p.LogTime,p.Code,p.Throughput from pfmlog_RealTime p

inner join (select a.Code,a.LogTime from pfmlog_RealTime a left join pfmlog_RealTime b

on a.Code = b.Code and a.LogTime <= b.LogTime group by a.ModuleName,a.Code,a.LogTime

having count(b.LogTime) <= 60) b1

on p.Code = b1.Code and p.LogTime = b1.LogTime

order by p.ModuleName;

sql2:效率高

select a.ModuleName,a.IP,a.LogTime,a.Code,a.Throughput from(

select b.*,

if(@pcode=b.Code and @pname=b.ModuleName,@rank:=@rank+1,@rank:=1) as rank,

@pcode:=b.Code,

@pname:=b.ModuleName

from pfmlog_RealTime b,(select @pcode := null,@pname := null ,@rank:=0) c

order by b.code,b.ModuleName,b.LogTime desc) a

where rank<=60

这条sql在workbench中执行没有问题，但放到C#代码中，程序运行就报“sql Fatal error encountered during command execution”。

解决：在sql连接串的末尾添加“;Allow User Variables=true”

参考：http://bbs.csdn.net/topics/390636133?page=1