一句SQL，求优化 、求调教~解决方法

一句SQL，求优化 、求调教~~！
求教给位达人：

SQL code

select * from Table a where exists(select * from Table where a.CARID =CARID  having a.CREATETIME =max(CREATETIME) ) and LINEID ='302' and UPDOWN = 1

查询所有 LINEID为302路车UPDOWN = 1（上行）车辆的最新（CREATETIME定位时间）信息，总觉得执行效率不高，求优化！！

还有的问题就max(CREATETIME)查出来的车辆有今天没有运行得出的就是昨天最后一笔记录，比如：302路的100号车，昨天的最后一条记录为(CREATETIME)22:23:56,今天停运，当我试查询最新的记录，如何将100号从最新记录中排出？？





[解决办法]
不要用*，把字段写出来
[解决办法]

SQL code

select top 1 * from Table a where     LINEID ='302'     and UPDOWN = 1order by CREATETIME desc
[解决办法]
SQL code
select * from Table a where exists(select * from Table where a.CARID =CARID  having a.CREATETIME =max(CREATETIME) ) and LINEID ='302' and UPDOWN = 11、首先 把你的 * 换成具体的字段。2、第二个 *  改用一个字段（CARID）或直接写 13、就是在你的条件上 建索引4、如果查询所得的列个数少，再建该列的 包含索引
[解决办法]
SQL code
select * from Table a where exists(select * from Table where a.CARID =CARID  having a.CREATETIME =max(CREATETIME) ) and LINEID ='302' and UPDOWN = 1--楼主为何写子查询呢select --所有字段from table a where   createtime=max(createtieme)and lineid='302' and updown=1order by CREATETIME desc
[解决办法]
select * from Table a where  
exists(select * from Table where a.CARID =CARID  having a.CREATETIME =max(CREATETIME) )
  and LINEID ='302' and UPDOWN = 1

select * from  
(
select *,id=row_number () over (partition by  CARID order by CREATETIME desc)
from table
where LINEID ='302' and UPDOWN = 1
) K
where id=1
[解决办法]
SQL code
--tryselect * from Table a where not  exists(select * from Table where a.CARID =CARID  and a.CREATETIME<CREATETIME )and LINEID ='302' and UPDOWN = 1
[解决办法]
SQL code
select * from Table a where a.CREATETIME = (select max(b.CREATETIME) from Table b where a.CARID =b.CARID) and LINEID ='302' and UPDOWN = 1/*这样应该可以了*/ 

 

查看更多 下一篇