两个地方,imdate条件最好改下,不要再字段上用函数 A.IMDATE >= date '2014-10-21' AND A.IMDATE < date '2014-10-21' + 1第二个,这里似乎用了分页,但是分页的方式不对,order by rn在那里没有意义 如果不需要排序,则在order by rn那里,加上条件and rownum<16 然后在最外层,加上order by rn,且在select后面加入提示/*+ first_rows(10)*/
E和F用的left join ,后面使用了 WHERE F.MC LIKE '%青铜%',这样查询出来的也不能涵盖E的全部记录,有点矛盾 根据你的业务逻辑调整,下面两种二选一E LEFT JOIN F ON E.WWID=F.WWID AND F.MC LIKE '%青铜%' E INNER JOIN F ON E.WWID=F.WWID WHERE F.MC LIKE '%青铜%'
SELECT * FROM V_SPECINFO A left join V_WWPICINFO D on A.WWID = D.WWID LEFT JOIN (SELECT F.MC FROM (SELECT WWID, MC FROM V_WWINFO UNION ALL SELECT WWID, MC FROM V_ZRLINFO)) F ON A.WWID = F.WWID WHERE A.XZQH = 'un0000' AND A.IMDATE = to_date('2014-10-21', 'YYYY-MM-DD') AND NOT EXISTS (SELECT 1 FROM V_CANCLE B WHERE A.WWID = B.WWID)
另外问下WWID是各个表的主键吗?
A.IMDATE >= date '2014-10-21' AND A.IMDATE < date '2014-10-21' + 1第二个,这里似乎用了分页,但是分页的方式不对,order by rn在那里没有意义
如果不需要排序,则在order by rn那里,加上条件and rownum<16
然后在最外层,加上order by rn,且在select后面加入提示/*+ first_rows(10)*/
根据你的业务逻辑调整,下面两种二选一E LEFT JOIN F ON E.WWID=F.WWID AND F.MC LIKE '%青铜%'
E INNER JOIN F ON E.WWID=F.WWID WHERE F.MC LIKE '%青铜%'
LIKE效率较低,建议改为
instr(F.MC,'青铜')>0
SELECT *
FROM V_SPECINFO A
left join V_WWPICINFO D on A.WWID = D.WWID
LEFT JOIN (SELECT F.MC
FROM (SELECT WWID, MC
FROM V_WWINFO
UNION ALL
SELECT WWID, MC FROM V_ZRLINFO)) F ON A.WWID = F.WWID
WHERE A.XZQH = 'un0000'
AND A.IMDATE = to_date('2014-10-21', 'YYYY-MM-DD')
AND NOT EXISTS (SELECT 1 FROM V_CANCLE B WHERE A.WWID = B.WWID)