我想每个类别只取-条数据,下面这语句执行好慢,有时好几十秒,有时又很快,是什么原因?
select t.ename,t.ntype,a.addtime,a.id,a.title from learn a inner join ntype t on t.ntypeid=a.ntypeid where a.flag=1 and 1 > (select count(id) from learn b where b.ntypeid= a.ntypeid and b.id> a.id)order by a.id desc limit 8
MYSQL里有没有类似MSSQL里的CROSS APPLY?
select t.ename,t.ntype,a.addtime,a.id,a.title from learn a inner join ntype t on t.ntypeid=a.ntypeid where a.flag=1 and 1 > (select count(id) from learn b where b.ntypeid= a.ntypeid and b.id> a.id)order by a.id desc limit 8
MYSQL里有没有类似MSSQL里的CROSS APPLY?
有时很快是因为开了query cache了吧
mysql没有crossapply
那就是我上面这写法有有问题,速度太慢了,,,
像这种需求怎么样实现会快些,自连?
select * from learn a where not exists(select 1 from learn where a.ntypeid=ntypeid and a.id<id)
FROM learn a
INNER JOIN ntype t ON t.ntypeid = a.ntypeid
WHERE a.flag =1
AND NOT
EXISTS (SELECT 1
FROM learn
WHERE a.ntypeid = ntypeid
AND a.id < id
)
LIMIT 8
这样确实快了很多。。
[征集]分组取最大N条记录方法征集,及散分....