报表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
三种方式有统计数据条数的差异,看是以实际数据,还是以费率表为主参照
本文名称:报表SQL
当前链接:http://scyanting.com/article/gpdggh.html