日期:2014-05-18  浏览次数:20449 次

求一个比现在这个效率更高的SQL语句和一个存储过程!
我有一个表,由ID和DATETIME同时做主键(就是说由一个时间和一个ID可以确定为一的值),我想通过一个SQL语句,达到查找比如ID = 2 和 ID =3 中离当前时间最近的两条记录。

我这样写了个语句能实现,不过是不是效率很低啊,请高手指点下。
select * from tbl_log where (tl_car =441 and tl_time = (select max(tl_time) tl_time from tbl_log where tl_car =441 ) ) or (tl_car =442 and tl_time = (select max(tl_time) tl_time from tbl_log where tl_car =442 ))


另外,如果用存储过程写一个类似的查询功能,可不可以不指定存储过程的参数个数,比如说我可以通过这个存储过程查询 id =2 ,和 id= 3的离当前时间最近的两条记录,也可以通过这个存储过程查询 id =2 ,id= 3 和id = 4 的离当前时间最近的三条记录。

------解决方案--------------------
SQL code
select 
    *
from 
    tbl_log a
where 
    tl_time=(select max(tl_time) from tbl_log where tl_car=a.tl_car and tl_car in(441,442))

------解决方案--------------------
your query looks ok. To write a procedure without setting a fixed list of ids, you can do something like this:

SQL code
create proc getRecentRec(@idList nvarchar(1000))
AS
Begin
    if charindex(',', @idList)>0 -- a list
    Begin
        declare @sql nvarchar(1000)
        set @sql = 
            'select * from tbl_log t
            where tl_car in (' + @idList + ')
            AND tl_time = (select max(tl_time) from tbl_log where tl_car = t.tl_car)'
        exec(@sql)
    End
else
    Begin
        select * from tbl_log t
        where tl_car = @idList And tl_time = (select max(tl_time) from tbl_log where tl_car = t.tl_car)
    End
End

------解决方案--------------------
SQL code
--静态——固定两个:tl_car in (441,442)
select * from tbl_log a where tl_car in (441,442) and tl_time=(select max(tl_time) from tbl_log where tl_car=a.tl_car)

--动态SQL查多个tl_car:
declare @tl_cars varchar(1000)
set @tl_cars='441,443,446,447'
exec ('select * from tbl_log a where tl_car in ('+@tl_cars+') and tl_time=(select max(tl_time) from tbl_log where tl_car=a.tl_car)')