功能要求 查询里记录 PType=1 and Status=2 and OType=1 最近一条 PType=1 and Status=1 and OType=1 记录,其中Status=2的Id 会大于 Status=1 的 Id,Id 是ta 表中主键
select a.*,
(select b.OperationDate from ta b where a.FileId=b.FileId and b.Status=1 and b.OType=1 and b.PType=1 order by Id desc limit 1)from ta a
where a.PType=1 and a.Status=2 and a.OType=1
and a.FileId=30826593
select a.*,
(select b.OperationDate from ta b where a.FileId=b.FileId and b.Status=1 and b.OType=1 and b.PType=1 order by Id desc limit 1)from ta a
where a.PType=1 and a.Status=2 and a.OType=1
and a.FileId=30826593
b.FileId加索引
select a.*,
(select b.OperationDate from ta b where a.FileId=b.FileId and b.Status=1 and b.OType=1 and b.PType=1
and
not exists(select 1 from ta where FileId=b.FileId and Status=b.Status and OType=b.OType and b.PType=PType and b.id<id ))
from ta a
where a.PType=1 and a.Status=2 and a.OType=1
and a.FileId=30826593create index xxx on ta (PType,Status,OType,FileId,id)
explain select a.*,
(select b.OperationDate from ta b where a.FileId=b.FileId and b.Status=1 and b.OType=1 and b.PType=1 order by Id desc limit 1)
from ta a
where a.PType=1 and a.Status=2 and a.OType=1
and a.FileId=30826593
select a.*,
(select max(b.OperationDate) from ta b where a.FileId=b.FileId and b.Status=1 and b.OType=1 and b.PType=1)
from ta a
where a.PType=1 and a.Status=2 and a.OType=1