SELECT Row,hotelName,hotelEnName,hotelCode,HotelLevel,CityID,cityName,addstate,addStateVal FROM
( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc) AS Row, T.* from
(select f.* from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal
from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName from mt_hotel a
left join syscity b on a.cityid=b.id ) d left join (select '已新增' as addState,hotelCode from hotelandhoteluser where supplierCode='MT0000016')
e on d.hotelCode=e.hotelCode ) f where 1=1 ) T where 1=1 ) TT WHERE TT.Row between 333333 and 333343
SELECT Row,hotelName,hotelEnName,hotelCode,HotelLevel,CityID,cityName,addstate,addStateVal FROM ( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc)AS Row, T.* from
(select f.* from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal
from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName from mt_hotel a
left join syscity b on a.cityid=b.id ) d left join (select '已新增' as addState,hotelCode from hotelandhoteluser where supplierCode='MT0000016') e on d.hotelCode=e.hotelCode )
f where 1=1 ) T where 1=1 ) TT WHERE TT.Row between 21 and 30
第一个耗时20多秒,第二个毫秒级别.. 语句一样,就页码不一样.. SQL菜鸟,求大神指导.
( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc) AS Row, T.* from
(select f.* from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal
from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName from mt_hotel a
left join syscity b on a.cityid=b.id ) d left join (select '已新增' as addState,hotelCode from hotelandhoteluser where supplierCode='MT0000016')
e on d.hotelCode=e.hotelCode ) f where 1=1 ) T where 1=1 ) TT WHERE TT.Row between 333333 and 333343
SELECT Row,hotelName,hotelEnName,hotelCode,HotelLevel,CityID,cityName,addstate,addStateVal FROM ( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc)AS Row, T.* from
(select f.* from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal
from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName from mt_hotel a
left join syscity b on a.cityid=b.id ) d left join (select '已新增' as addState,hotelCode from hotelandhoteluser where supplierCode='MT0000016') e on d.hotelCode=e.hotelCode )
f where 1=1 ) T where 1=1 ) TT WHERE TT.Row between 21 and 30
第一个耗时20多秒,第二个毫秒级别.. 语句一样,就页码不一样.. SQL菜鸟,求大神指导.
select top 10 * from [dbo].[Test]
总条数select COUNT(1) from [dbo].[Test]
简化的查询语句
select * from (
select ROW_NUMBER() over(order by pname desc ) rowid, * from Test ) A
where rowid between 21 and 30select * from (
select ROW_NUMBER() over (order by pname desc ) rowid, * from Test ) A
where rowid between 333333 and 333343查询出来,是1秒之内就可以的。
那么应该和页码没有关系。
我觉得你试试把left join去掉,然后ROW_NUMBER() OVER (order by T.hotelCode desc) 换ct
FROM ( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc) AS Row,
T.*
from (select f.*
from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal
from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName
from mt_hotel a
left join syscity b on a.cityid=b.id ) d
left join (select '已新增' as addState,hotelCode
from hotelandhoteluser
where supplierCode='MT0000016') e on d.hotelCode=e.hotelCode ) f
where 1=1 ) T
where 1=1 ) TT
WHERE TT.Row between 333333 and 333343