起初的查询语句:
select * from mall_region_show t1 left join mall_shops_base t2 on t1.shopid=t2.id
where t1.state=0
and t1.type=1
and TO_DATE(T2.effecttime, 'yyyy-MM-dd') > SYSDATE
and t2.isopen=0 and rownum<10
order by t1.orders asc,t1.createtime desc
修改后:
select * from (select * from mall_region_show t1 left join mall_shops_base t2 on t1.shopid=t2.id
where t1.state=0and t1.type=1
and TO_DATE(T2.effecttime, 'yyyy-MM-dd') > SYSDATE
and t2.isopen=0
order by t1.orders asc,t1.createtime desc) tt where rownum<11
这个两个sql语句查询的结果不一样,
第一个sql并不会像我们要的那样,将所有的结果按照orders排序查出来,再取前10个,因为rownum会将查询出的结果重新排序,再取前10个
第二个sql语句,就是将查询结果按照orders排序,再取前10个