注,数据库为 816
现SQL脚本
SELECT Bi.DFNY,Bi.UserNo,Hbi.Date,Mcv.CreaditVal FROM PJ Bi,MonthVal Mcv WHERE Mcv.UserNo(+) = Bi.HH AND Mcv.ElectroDate(+) = Bi.DFNY
UNION ALL
SELECT Hbi.DFNY,Hbi.UserNo,Hbi.Date,Mcv.CreaditVal FROM LS_PJ Hbi ,MonthVal Mcv WHERE NOT EXISTS (SELECT 1 FROM MonthCreditVal Mcv WHERE ForbidAmend =''T'' AND Mcv.UserNo = Hbi.HH AND Mcv.ElectroDate = Hbi.DFNY) AND Hbi.HH = Mcv.UserNo(+) AND Hbi.DFNY = Mcv.ElectroDate(+)
上脚本注解:
PJ(票据) 和 LS_PJ(历史票据) ,这两个表数据是相互排斥的.新出来的票据在PJ表中,但该票据结完成便进入LS_PJ表且PJ表中就不成在了.现是要查PJ + LS_PJ所以票据信息. 所以使用的 UNION ALL 把两个表所有的数据结合起来了.
但SELECT Hbi.DFNY,Mcv.CreaditVal FROM LS_PJ Hbi ,MonthVal Mcv WHERE NOT EXISTS (SELECT 1 FROM MonthVal Mcv WHERE ForbidAmend =''T'' AND Mcv.UserNo = Hbi.HH AND Mcv.ElectroDate = Hbi.DFNY) AND Hbi.HH = Mcv.UserNo(+) AND Hbi.DFNY = Mcv.ElectroDate(+)该语句又有一个过滤功能.
注:因为LS_PJ表中的数据计算完后在MonthVal表中有存根.上面的意思是:但在MonthVal表中有存根的数据就不从LS_PJ信息表中取数据了.在MonthVal表中未有存根时再从LS_PJ表中取数据
我想问的是这条过滤可以换种效率更高的写法吗??
还有就是正面一个功能
SELECT NVL(SUM(JE),0) INTO OnSchedule FROM WHERE HH = UserNo AND SJ <= Date; //Date是上面查询出来的
请问一下这条语句能于上面联合到一起吗
谢谢了.
现SQL脚本
SELECT Bi.DFNY,Bi.UserNo,Hbi.Date,Mcv.CreaditVal FROM PJ Bi,MonthVal Mcv WHERE Mcv.UserNo(+) = Bi.HH AND Mcv.ElectroDate(+) = Bi.DFNY
UNION ALL
SELECT Hbi.DFNY,Hbi.UserNo,Hbi.Date,Mcv.CreaditVal FROM LS_PJ Hbi ,MonthVal Mcv WHERE NOT EXISTS (SELECT 1 FROM MonthCreditVal Mcv WHERE ForbidAmend =''T'' AND Mcv.UserNo = Hbi.HH AND Mcv.ElectroDate = Hbi.DFNY) AND Hbi.HH = Mcv.UserNo(+) AND Hbi.DFNY = Mcv.ElectroDate(+)
上脚本注解:
PJ(票据) 和 LS_PJ(历史票据) ,这两个表数据是相互排斥的.新出来的票据在PJ表中,但该票据结完成便进入LS_PJ表且PJ表中就不成在了.现是要查PJ + LS_PJ所以票据信息. 所以使用的 UNION ALL 把两个表所有的数据结合起来了.
但SELECT Hbi.DFNY,Mcv.CreaditVal FROM LS_PJ Hbi ,MonthVal Mcv WHERE NOT EXISTS (SELECT 1 FROM MonthVal Mcv WHERE ForbidAmend =''T'' AND Mcv.UserNo = Hbi.HH AND Mcv.ElectroDate = Hbi.DFNY) AND Hbi.HH = Mcv.UserNo(+) AND Hbi.DFNY = Mcv.ElectroDate(+)该语句又有一个过滤功能.
注:因为LS_PJ表中的数据计算完后在MonthVal表中有存根.上面的意思是:但在MonthVal表中有存根的数据就不从LS_PJ信息表中取数据了.在MonthVal表中未有存根时再从LS_PJ表中取数据
我想问的是这条过滤可以换种效率更高的写法吗??
还有就是正面一个功能
SELECT NVL(SUM(JE),0) INTO OnSchedule FROM WHERE HH = UserNo AND SJ <= Date; //Date是上面查询出来的
请问一下这条语句能于上面联合到一起吗
谢谢了.
SELECT Hbi.DFNY, Hbi.UserNo, Hbi.Date, Mcv.CreaditVal
FROM (SELECT DFNY, UserNo, Date FROM PJ
UNION
SELECT DFNY, UserNo, Date FROM LS_PJ) Hbi,
MonthVal Mcv,
(SELECT UserNo || ElectroDate as tt
FROM MonthCreditVal
WHERE ForbidAmend = '' T '') a1
WHERE Hbi.HH || Hbi.DFNY <> a1.tt
AND Hbi.HH = Mcv.UserNo(+)
AND Hbi.DFNY = Mcv.ElectroDate(+)
说明:
1、过滤功能的语句改成 a1表,之后 Hbi.HH || Hbi.DFNY <> a1.tt。
2、既然PJ(票据)和LS_PJ(历史票据),相互排斥,直接union后再与就MonthVal Mcv关联就可以了。
-------------------------------
还有就是正面一个功能
SELECT NVL(SUM(JE),0) INTO OnSchedule FROM WHERE HH = UserNo AND SJ <= Date; //Date是上面查询出来的
-------------------------------
请问一下这条语句能于上面联合到一起吗 >不过Date值应该是一个范围,你的这个语句应该是循环中每次取一个Date值吧?好像可以联合,需要你把要求补足!
试试:
SELECT Hbi.DFNY, Hbi.UserNo, Hbi.Date, Mcv.CreaditVal
FROM (SELECT DFNY, UserNo, Date FROM PJ
UNION
SELECT DFNY, UserNo, Date FROM LS_PJ) Hbi,
MonthVal Mcv,
(SELECT UserNo || ElectroDate as tt
FROM MonthCreditVal
WHERE ForbidAmend = '' T '') a1
WHERE Hbi.HH || Hbi.DFNY <> a1.tt
AND Hbi.HH = Mcv.UserNo(+)
AND Hbi.DFNY = Mcv.ElectroDate(+)修正:
SELECT Hbi.DFNY, Hbi.UserNo, Hbi.Date, Mcv.CreaditVal
FROM (SELECT DFNY, UserNo, Date
FROM PJ
UNION
SELECT DFNY, UserNo, Date FROM LS_PJ) Hbi,
MonthVal Mcv
WHERE Hbi.HH || Hbi.DFNY not in
(SELECT UserNo || ElectroDate as tt
FROM MonthCreditVal
WHERE ForbidAmend = '' T '')
AND Hbi.HH = Mcv.UserNo(+)
AND Hbi.DFNY = Mcv.ElectroDate(+)
SELECT /* ALL _ROWS*/ --add
Hbi.DFNY, Hbi.UserNo, Hbi.Date, Mcv.CreaditVal
FROM (SELECT DFNY, UserNo, Date
FROM PJ
UNION
SELECT DFNY, UserNo, Date FROM LS_PJ) Hbi,
MonthVal Mcv
WHERE Hbi.HH || Hbi.DFNY not in
(SELECT /*+ INDEX(MonthCreditVal MonthCreditVal_PK) */ --add
UserNo || ElectroDate as tt
FROM MonthCreditVal
WHERE ForbidAmend = '' T '')
AND Hbi.HH = Mcv.UserNo(+)
AND Hbi.DFNY = Mcv.ElectroDate(+)
>行,哪个高就用哪一个吧。
/* ALL _ROWS*/ 我就不懂什么意思了.
>这些都是oracle定义的一些优化语句,其实不影响数据的结果,只影响执行的规则和效率。
具体的含义是:/* ALL_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
当然,还有好多,我也只是使用,没去追真正的内涵。你上网查一下还有好多,如:
/* FIRST_ROWS*/
/* CHOOSE*/
...等等。
...
from tab1 A,tab2 B,tab2 C
...说实话,我也不知道真正的含义,总之速度提高,客户通过了。追究根源要问csdn里面的星星了!