Question

sql 根据时间模糊查询，并分页。为什么加上时间条件以后，分页就没效果了

这是sql ，2中方式，加上and CONVERT(varchar(100), startdatetime, 23) like '2010-08-26%' 都不行，请高手指教

select * from (select *,row_number() over(order by startdatetime desc) rn
from WX_PBX_Call) a where rn between 0 and 10
and CONVERT(varchar(100), startdatetime, 23) like '2010-08-26%'
order by startdatetime desc

select top 10 * from wx_pbx_call where callid not in(select top 0 callid from wx_pbx_call order by startdatetime desc)
and CONVERT(varchar(100), startdatetime, 23) like '2010-08-26%'
order by startdatetime desc
如果不加上 and CONVERT(varchar(100), startdatetime, 23) like '2010-08-26%' 这句代码，分页就没问题

Answer 1

你这个条件必须也应用到内层啊.

select top 10 *
from wx_pbx_call
where callid not in(
select top 0 callid
from wx_pbx_call
where CONVERT(varchar(100), startdatetime, 23) like '2010-08-26%'
order by startdatetime desc
)
and
CONVERT(varchar(100), startdatetime, 23) like '2010-08-26%'
order by startdatetime desc

不知道你用的什么数据库, sql 2005还是sql 2008里面有新的分页方法,效率要高不少. 你搜搜看看.

Answer 2

把 CONVERT(varchar(100), startdatetime, 23) like '2010-08-26%'
修改成

startdatetime >=‘2010-8-26 ' and startdatetime <‘2010-8-27'

用这个试试吧