为什么我用创建视图的sql语句查询时的记录条数比用该视图时记录条数要多。

Question

我想该不会的. 看看你的SQL和系统中的SQL是不是一样呢?或许是关联上出了些问题.比如少个(+)什么的.

Answer 1

这是视图：
CREATE OR REPLACE VIEW V_RETIREREGISTER ( COMPID, 
COMPNAME, EXTENDNAME, ID, INSUREID, 
PERSONNAME, SEX, NATION, BIRTHDAY, 
LSLOCAL, ADDRESS, STREETCODE, ZIP, 
USESTATUS, DEGREE, PLACELEVEL, TECHPOST, 
BUSINESS, WORKTIME, TOTALYEAR, REGARDYEAR, 
ALLPAYYEAR, YEAR98, STOPTIME, PERSONACCOUNT, 
PERSONPAY, RETIRETYPE, JUDGENO, RETIRETIME, 
ADDDATE, WAGEBEFORERETIRE, SCALE104, ANNUITY008, 
ENTERPRISESCALE, EMPAVGWAGE, AVGWAGE, SCALE278, 
STARTSCALE, STARTAMMOUNT, BASESCALE, INDEXAVGPAY, 
PASSSCALE, PASSCVALUE, PASSANNUITY, ACCOUNTANNUITY, 
ACCOUNTSUBSIDY, FIRSTANNUITY, BASESUBSIDY, LIVESUBSIDY, 
PENURYSUBSIDY, PRICESUBSIDY, OTHERSUBSIDY, FRONTSUBSIDY
 ) AS 
select a.compid,b.name comp,a.extendname,a.id,a.insureid,a.name,decode(a.sex,'0','男','1','女')sex,c.name nation,
a.birthday,'' IsLocal,a.address,'' Streetcode,a.zip,d.name usestatus,
decode(a.degree,'0','干部','1','工人','2','军转干部','3','复退军人','4','军转干部转工人','5','复退军人提干部',
'6','干部船员转工人')degree,
a.placelevel,e.name techpost,f.name business,a.worktime,
round((a.allpayyear+a.regardmonth)/12,1)totalyear,round(a.regardmonth/12,1)regardyear,
round(a.allpayyear/12,1)allpayyear,round(a.year98/12,1)year98,round(a.stoptime/12,1)stoptime,
a.personaccount,a.personpay,h.name retiretype,'' JUDGENO,
a.retiretime,'' AddDate,0 Wagebeforeretire,0 scale104,0 annuity008,0 enterprisescale,i.avgwage empavgwage,j.avgwage,
0 scale278,0 startscale,0 startammount,k.scale basescale,k.indexavgpay,k.passscale*100 passscale,
k.passcvalue,k.passannuity,k.accountannuity,k.accountsubsidy,k.firstannuity,0 Basesubsidy,0 livesubsidy,
0 penurysubsidy,0 pricesubsidy,0 othersubsidy,k.frontsubsidy
from retire a 
inner join company b on b.compid=a.compid 
left join nation c on c.nation=a.nation 
left join usestatus d on d.usestatus=a.usestatus 
left join techpost e on e.techpost=a.techpost 
left join business f on f.business=a.business 
inner join retiretype h on h.RETIRETYPE=a.retiretype 
left join base i on i.id=a.id and i.insureid=a.insureid and i.time=to_number(substr(a.retiretime,1,4))-1
left join avgwage j on j.province=b.area and j.time=to_number(substr(a.RETIRETIME,1,4))-1
left join annuity_adjust k on k.id=a.id and k.insureid=a.insureid and k.time=(select max(time) 
from annuity_adjust where id=a.id and insureid=a.insureid);这是SQL语句，源自上面的视图：select a.compid,b.name comp,a.extendname,a.id,a.insureid,a.name,decode(a.sex,'0','男','1','女')sex,c.name nation,
a.birthday,'' IsLocal,a.address,'' Streetcode,a.zip,d.name usestatus,
decode(a.degree,'0','干部','1','工人','2','军转干部','3','复退军人','4','军转干部转工人','5','复退军人提干部',
'6','干部船员转工人')degree,
a.placelevel,e.name techpost,f.name business,a.worktime,
round((a.allpayyear+a.regardmonth)/12,1)totalyear,round(a.regardmonth/12,1)regardyear,
round(a.allpayyear/12,1)allpayyear,round(a.year98/12,1)year98,round(a.stoptime/12,1)stoptime,
a.personaccount,a.personpay,h.name retiretype,'' JUDGENO,
a.retiretime,'' AddDate,0 Wagebeforeretire,0 scale104,0 annuity008,0 enterprisescale,i.avgwage empavgwage,j.avgwage,
0 scale278,0 startscale,0 startammount,k.scale basescale,k.indexavgpay,k.passscale*100 passscale,
k.passcvalue,k.passannuity,k.accountannuity,k.accountsubsidy,k.firstannuity,0 Basesubsidy,0 livesubsidy,
0 penurysubsidy,0 pricesubsidy,0 othersubsidy,k.frontsubsidy
from retire a 
inner join company b on b.compid=a.compid 
left join nation c on c.nation=a.nation 
left join usestatus d on d.usestatus=a.usestatus 
left join techpost e on e.techpost=a.techpost 
left join business f on f.business=a.business 
inner join retiretype h on h.RETIRETYPE=a.retiretype 
left join base i on i.id=a.id and i.insureid=a.insureid and i.time=to_number(substr(a.retiretime,1,4))-1
left join avgwage j on j.province=b.area and j.time=to_number(substr(a.RETIRETIME,1,4))-1
left join annuity_adjust k on k.id=a.id and k.insureid=a.insureid and k.time=(select max(time) 
from annuity_adjust where id=a.id and insureid=a.insureid);结果 是：select * from V_RETIREREGISTER 的记录数不等于上面的SQL语句的执行完后的记录数。