select a.id,
a.claimno ,
a.username ,
a.appealreason ,
a.appealstatus,
a.appealResult,
b.tasksuggestion ,
b.updater ,
(select tasksuggestion from claimTasks where tasktype='ZJPD' and taskid=a.claimno) ptasksuggestion
from
appealInfo a ,
claimTasks b
where
b.tasktype='SSPD' and a.claimno=b.taskid and
appealFlag='1' and 1=1
order by id desc目前执行时间太长了3.563s 2000多条数据
(select tasksuggestion from claimTasks where tasktype='ZJPD' and taskid=a.claimno) ptasksuggestion你到底想要什么功能啊
A.CLAIMNO,
A.USERNAME,
A.APPEALREASON,
A.APPEALSTATUS,
A.APPEALRESULT,
B.TASKSUGGESTION,
B.UPDATER,
-- (select tasksuggestion from claimTasks where tasktype='ZJPD' and taskid=a.claimno) ptasksuggestion
--以上相对占用资源,可改成decode的写法
DECODE(B.TASKTYPE, 'ZJPD', B.TASKSUGGESTION)
FROM APPEALINFO A, CLAIMTASKS B
WHERE B.TASKTYPE = 'SSPD'
AND A.CLAIMNO = B.TASKID
AND APPEALFLAG = '1'
AND 1 = 1
ORDER BY ID DESC
原来的写法是没有多少问题的,逻辑比较简单。
一个建议是,表连接的时候,把数据量大的表放在前面,量小的表放之后。因为SQL运行时从后往前去读表的