报表SQL

--子查询方式
select r.organno,r.organname,sum(r.vrc),sum(r.psatf),sum(r.vtf),(select a.rate from tb_accrate a where a.organno=r.organno and a.accounttype='03' ) from TBC_QRCODEPOISE_REP r group by r.organno,r.organname;
-- 关联查询方式
SELECT T.*, A.RATE FROM (SELECT R.ORGANNO, R.ORGANNAME, SUM(R.VRC), SUM(R.PSATF), SUM(R.VTF)
FROM TBC_QRCODEPOISE_REP R GROUP BY R.ORGANNO, R.ORGANNAME) T
LEFT JOIN TB_ACCRATE A
ON T.ORGANNO = A.ORGANNO
AND A.ACCOUNTTYPE = '03'
WHERE T.ORGANNO IN (SELECT A.ORGANNO FROM TB_ACCRATE A WHERE A.ACCOUNTTYPE = '03');

-- 按照费率表的数量来展示
SELECT A.ORGANNO,O.ORGANNAME,A.RATE,nvl(T.VRC,0),nvl(T.PSATF,0),nvl(T.VTF,0) FROM TB_ACCRATE A
LEFT JOIN TB_ORGAN O ON A.ORGANNO=O.ORGANNO
LEFT JOIN (SELECT R.ORGANNO, R.ORGANNAME, SUM(R.VRC) VRC, SUM(R.PSATF) PSATF, SUM(R.VTF) VTF
FROM TBC_QRCODEPOISE_REP R where R.WORKDATE>=TO_DATE('2018-02-25','yyyy-mm-dd') AND R.WORKDATE<=TO_DATE('2018-02-27','yyyy-mm-dd') GROUP BY R.ORGANNO, R.ORGANNAME) T ON A.ORGANNO = T.ORGANNO
WHERE A.ACCOUNTTYPE = '03' ORDER BY A.ORGANNO

三种方式有统计数据条数的差异，看是以实际数据，还是以费率表为主参照

转载于:https://blog.51cto.com/hebinteng/2094309