最近同事处理一个客户发现一个bug,在排序的时候出现问题:
可能是数据库的bug
field002这里有个”2008-03-02“的排序,其他都是按field002来排序,个别不是按field002排序。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLi0zaHRGcWdUYuVzVa9GczoVdG1mWfVGc5RHLwkzX39GZhh2csATMflHLwEzX4xSZz91ZsADMx8FdsYkRGZkRG9lcvx2bjxSa2EWNhJTW1AlUxEFeVRUUfRHelRHL2EzXlpXazxyayFWbyVGdhd3LcV2Zh1Wa9M3clN2byBXLzN3btg3PnVGcq5CNwMGNkFDZmRDMlVjZ4UzYiRzY0czYzkzNzYWZlBTNk9CXxEzLchDMxIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLzM3Lc9CX6MHc0RHaiojIsJye.jpeg)
执行语句
select * from (
select id,field001 as field001,field002 as field002,
(SELECT objname FROM customer WHERE tbalias.field003=id) as field003,field003 as field003_id,
requestid, ROW_NUMBER() OVER ( order by tbalias.field002 asc) as pos --
from td tbalias
where exists ( select 'X' from dd wb where tbalias.requestid=wb.id and wb.isdelete=0 and isfinished=0 )
)
as T where T.pos>0 and T.pos<=20
以下可以解决按field002排序问题
1,不用分页
2,将customer中的id唯一索引从改成非唯一索引
3,去掉(SELECT objname FROM customer WHERE tbalias.field003=id) as field003
但这些因为业务逻辑都不能去掉
看了他的执行计划:在和customer 查询时,是用的嵌套查询,驱动表是td。按算法是先从td表中查的一条数据,再和customer中比较得到出数据。
在百思不得其解后:最后修改了一下sql,将子查询变成关联查询就好了,代码如下:
bb.objname as field003,field003 as field003_id,
from td tbalias left join customer bb on tbalias.field003=bb.id