最近同僚處理一個客戶發現一個bug,在排序的時候出現問題:
可能是資料庫的bug
field002這裡有個”2008-03-02“的排序,其他都是按field002來排序,個别不是按field002排序。
執行語句
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