sql语句(1)
SELECT PAIDBAND,
ricurr,
sum(Qslosscount) Qslosscount,
sum(QsPaid) QsPaid,
sum(QsLoss) QsLoss,
sum(QsIncurred) QsIncurred,
sum(Spllosscount) Spllosscount,
sum(SplPaid) SplPaid,
sum(SplLoss) SplLoss,
sum(SplIncurred) SplIncurred
FROM (select claimno,
ricurr,
nvl(claim.Qslosscount, 0) Qslosscount,
nvl(claim.QsPaid, 0) QsPaid,
nvl(claim.QsLoss, 0) QsLoss,
nvl(QsPaid + QsLoss, 0) QsIncurred,
nvl(claim.Spllosscount, 0) Spllosscount,
nvl(claim.SplPaid, 0) SplPaid,
nvl(claim.SplLoss, 0) SplLoss,
nvl(SplPaid + SplLoss, 0) SplIncurred,
case
when claim.insuredloss >= 0 and claim.insuredloss <= 1000000 then
'0-1000000'
when claim.insuredloss >= 1000001 and
claim.insuredloss <= 10000000 then
'1000001-10000000'
when claim.insuredloss >= 1000001 then
'1000001-above'
else
'M'
end paidBand
FROM (select claimno,
ricurr,
sum(losscount) losscount,
sum(PaidLoss + OsLoss) insuredloss,
sum(QsLoss) QsLoss,
sum(SplLoss) SplLoss,
sum(QsPaid) QsPaid,
sum(SplPaid) SplPaid,
sum(Qslosscount) Qslosscount,
sum(Spllosscount) Spllosscount
from (select claimno,
ricurr,
count(distinct fpay.claimno) losscount,
sum(PaidLoss) PaidLoss,
sum(OsLoss) OsLoss,
sum(QsLoss) QsLoss,
sum(SplLoss) SplLoss,
sum(QsPaid) QsPaid,
sum(SplPaid) SplPaid,
sum(distinct(decode(fpay.ttytype, '31', 1, 0))) Spllosscount,
sum(distinct(decode(fpay.ttytype, '21', 1, 0))) Qslosscount
from (SELECT s.claimno,
s.ttytype,
s.ricurr,
count(distinct s.claimno) losscount,
sum(s.PAIDSUM) PaidLoss,
sum(decode(s.ttytype, '31', s.PAIDSUM, 0)) SplPaid,
sum(decode(s.ttytype, '21', s.PAIDSUM, 0)) QsPaid,
0 OsLoss,
0 SplLoss,
0 QsLoss
from GRReclaimShare s, GRReclaim c
WHERE s.RECLAIMNO IN
(SELECT a.RECLAIMNO
FROM GRReclaimShare a
WHERE (a.ttyid, a.sectno) IN
(select gr.ttyid, gr.SECTNO
from grttysect gr
WHERE gr.SectClass in ('2')))
AND s.paydate <= DATE
'&date' AND s.RECLAIMNO = c.RECLAIMNO
AND c.startdate BETWEEN DATE
'&uwyear-01-01'
AND DATE
'&uwyear-12-31'
AND s.ttytype IN ('21', '31')
group BY s.claimno, s.ttytype, s.ricurr
union ALL
SELECT b.claimno,
a.ttytype,
a.ricurr,
count(distinct b.claimno) losscount,
0 PaidLoss,
0 QsPaid,
0 SplPaid,
sum(a.OSSUM) OsLoss,
sum(decode(a.ttytype, '31', a.OSSUM, 0)) SplLoss,
sum(decode(a.ttytype, '21', a.OSSUM, 0)) QsLoss
FROM GROSRECLAIMSHAREHIS a, GROSRECLAIMHIS b
WHERE b.CLAIMNO = a.CLAIMNO
AND a.ttytype IN ('21', '31')
AND (a.claimno, a.accym) IN
(SELECT claimno, max(accym) accym
FROM GROSRECLAIMSHAREHIS
WHERE (ttyid, sectno) IN
(select gr.ttyid, gr.SECTNO
from grttysect gr
WHERE gr.sectclass IN
('1 ', '2', '3 '))
AND accym <=
to_char(DATE '&date',
'yyyymm')
GROUP BY claimno)
AND (b.claimno, b.accym) IN
(SELECT claimno, max(accym) accym
FROM GROSRECLAIMSHAREHIS
WHERE (ttyid, sectno) IN
(select gr.ttyid, gr.SECTNO
from grttysect gr
WHERE gr.sectclass IN
('1 ', '2', '3 '))
AND accym <=
to_char(DATE '&date',
'yyyymm')
GROUP BY claimno)
AND b.startdate BETWEEN DATE
'&uwyear-01-01'
AND DATE '&uwyear-12-31'
group BY b.claimno, a.ttytype, a.ricurr) fpay
group BY claimno, ricurr) claim
GROUP BY claimno, ricurr) claim) ll
GROUP BY PAIDBAND, ricurr
sql语句(2)
SELECT PAIDBAND,
ricurr,
sum(Qslosscount) Qslosscount,
sum(QsPaid) QsPaid,
sum(QsLoss) QsLoss,
sum(QsIncurred) QsIncurred,
sum(Spllosscount) Spllosscount,
sum(SplPaid) SplPaid,
sum(SplLoss) SplLoss,
sum(SplIncurred) SplIncurred
FROM (select claimno,
ricurr,
nvl(claim.Qslosscount, 0) Qslosscount,
nvl(claim.QsPaid, 0) QsPaid,
nvl(claim.QsLoss, 0) QsLoss,
nvl(QsPaid + QsLoss, 0) QsIncurred,
nvl(claim.Spllosscount, 0) Spllosscount,
nvl(claim.SplPaid, 0) SplPaid,
nvl(claim.SplLoss, 0) SplLoss,
nvl(SplPaid + SplLoss, 0) SplIncurred,
case
when claim.insuredloss >= 0 and claim.insuredloss <= 1000000 then
'0-1000000'
when claim.insuredloss >= 1000001 and
claim.insuredloss <= 10000000 then
'1000001-10000000'
when claim.insuredloss >= 1000001 then
'1000001-above'
else
'M'
end paidBand
FROM (select claimno,
ricurr,
sum(losscount) losscount,
sum(PaidLoss + OsLoss) insuredloss,
sum(QsLoss) QsLoss,
sum(SplLoss) SplLoss,
sum(QsPaid) QsPaid,
sum(SplPaid) SplPaid,
sum(Qslosscount) Qslosscount,
sum(Spllosscount) Spllosscount
from (select claimno,
ricurr,
count(distinct fpay.claimno) losscount,
sum(PaidLoss) PaidLoss,
sum(OsLoss) OsLoss,
sum(QsLoss) QsLoss,
sum(SplLoss) SplLoss,
sum(QsPaid) QsPaid,
sum(SplPaid) SplPaid,
sum(distinct(decode(fpay.ttytype, '31', 1, 0))) Spllosscount,
sum(distinct(decode(fpay.ttytype, '21', 1, 0))) Qslosscount
from (SELECT s.claimno,
s.ttytype,
s.ricurr,
count(distinct s.claimno) losscount,
sum(s.PAIDSUM) PaidLoss,
sum(decode(s.ttytype, '31', s.PAIDSUM, 0)) SplPaid,
sum(decode(s.ttytype, '21', s.PAIDSUM, 0)) QsPaid,
0 OsLoss,
0 SplLoss,
0 QsLoss
from GRReclaimShare s, GRReclaim c
WHERE s.RECLAIMNO IN
(SELECT a.RECLAIMNO
FROM GRReclaimShare a
WHERE (a.ttyid, a.sectno) IN
(select gr.ttyid, gr.SECTNO
from grttysect gr
WHERE gr.SectClass in ('2')))
AND s.paydate <= DATE
'&date' AND s.RECLAIMNO = c.RECLAIMNO
AND c.startdate BETWEEN DATE
'&uwyear-01-01'
AND DATE
'&uwyear-12-31'
AND s.ttytype IN ('21', '31')
group BY s.claimno, s.ttytype, s.ricurr
union ALL
SELECT b.claimno,
a.ttytype,
a.ricurr,
count(distinct b.claimno) losscount,
0 PaidLoss,
0 QsPaid,
0 SplPaid,
sum(a.OSSUM) OsLoss,
sum(decode(a.ttytype, '31', a.OSSUM, 0)) SplLoss,
sum(decode(a.ttytype, '21', a.OSSUM, 0)) QsLoss
FROM GROSRECLAIMSHAREHIS a, GROSRECLAIMHIS b
WHERE b.CLAIMNO = a.CLAIMNO
AND a.ttytype IN ('21', '31')
AND (a.claimno, a.accym) IN
(SELECT claimno, max(accym) accym
FROM GROSRECLAIMSHAREHIS
WHERE (ttyid, sectno) IN
(select gr.ttyid, gr.SECTNO
from grttysect gr
WHERE gr.sectclass IN
('1', '2', '3'))
AND accym <=
to_char(DATE '&date',
'yyyymm')
GROUP BY claimno)
AND (b.claimno, b.accym) IN
(SELECT claimno, max(accym) accym
FROM GROSRECLAIMSHAREHIS
WHERE (ttyid, sectno) IN
(select gr.ttyid, gr.SECTNO
from grttysect gr
WHERE gr.sectclass IN
('1', '2', '3'))
AND accym <=
to_char(DATE '&date',
'yyyymm')
GROUP BY claimno)
AND b.startdate BETWEEN DATE
'&uwyear-01-01'
AND DATE '&uwyear-12-31'
group BY b.claimno, a.ttytype, a.ricurr) fpay
group BY claimno, ricurr) claim
GROUP BY claimno, ricurr) claim) ll
GROUP BY PAIDBAND, ricurr
我通过对比软件对比
完全一致,但是查询出来的结果如图:
sql(1)
sql(2)
不知道咋回事。
2019-08-20补充:
传参带空格了
我淦