我有一个表，由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 awhere 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))ASBegin 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) Endelse 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) EndEnd
------解决方案--------------------
- 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 ([email protected]_cars+') and tl_time=(select max(tl_time) from tbl_log where tl_car=a.tl_car)')