收费项目表(CHARGE_ITEM_INFO)
CHARGE_ITEM_NO(收费项目编号) REGISTRATION_NO(挂号) PAID_UP(实收金额),DISCOUNT(折扣金额)
001 A001 1100 100
002 A002 120 0
003 A003 300 20
收费记录表(SHOUFEI_INFO)
CHARGE_NO(收费编号) REGISTRATION_NO(挂号) QF_PAMT(本次欠费金额) QIANFEI(交欠费金额)
1 A001 1000 0
2 A001 0 800
3 A001 0 100
4 A001 0 100
5 A002 100 0
6 A003 100 0
7 A003 0 100要求统计,实收金额,折扣金额,剩余欠费
我写了sql语句,但统计的数据不对,还请大神指点SELECT SUM(PAID_UP),SUM(DISCOUNT),SUM(QF_PAMT-QIANFEI) FROM CHARGE_ITEM_INFO A
LEFT OUTER JOIN SPLTY8.SHOUFEI_INFO B ON B.REGISTRATION_NO =A.REGISTRATION_NO
急求 sql 表查询 急急急急急
CHARGE_ITEM_NO(收费项目编号) REGISTRATION_NO(挂号) PAID_UP(实收金额),DISCOUNT(折扣金额)
001 A001 1100 100
002 A002 120 0
003 A003 300 20
收费记录表(SHOUFEI_INFO)
CHARGE_NO(收费编号) REGISTRATION_NO(挂号) QF_PAMT(本次欠费金额) QIANFEI(交欠费金额)
1 A001 1000 0
2 A001 0 800
3 A001 0 100
4 A001 0 100
5 A002 100 0
6 A003 100 0
7 A003 0 100要求统计,实收金额,折扣金额,剩余欠费
我写了sql语句,但统计的数据不对,还请大神指点SELECT SUM(PAID_UP),SUM(DISCOUNT),SUM(QF_PAMT-QIANFEI) FROM CHARGE_ITEM_INFO A
LEFT OUTER JOIN SPLTY8.SHOUFEI_INFO B ON B.REGISTRATION_NO =A.REGISTRATION_NO
急求 sql 表查询 急急急急急
(SELECT registration_no,SUM(qf_pamt) qf_pamt,SUM(qianfei) qianfei FROM shoufei_info
GROUP BY registration_no) b
WHERE a.registration_no = b.registration_no
Here I can't ensure if I'm correct, if the code is not helpful, please let me know!
REGISTRATION_NO PAID_UP DISCOUNT Rest_mn
------------------------------------------------------------ ---------- ---------- ----------
A003 300 20 0
A001 1100 100 0
A002 120 0 100
LEFT JOIN (select REGISTRATION_NO,sum(QF_PAMT)-sum(QIANFEI) as yuE from SHOUFEI_INFO group by REGISTRATION_NO,yeE) b ON B.REGISTRATION_NO =A.REGISTRATION_NO
一起研究oracle,qq:34246450 有事常联系!
SELECT A.PAID_UP,A.DISCOUNT,b.yuE FROM CHARGE_ITEM_INFO A
LEFT JOIN (select REGISTRATION_NO,sum(QF_PAMT)-sum(QIANFEI) as yuE from SHOUFEI_INFO group by REGISTRATION_NO) b ON b.REGISTRATION_NO =A.REGISTRATION_NO
FROM (SELECT REGISTRATION_NO,
SUM(PAID_UP) AS PAID_UP,
SUM(DISCOUNT) AS DISCOUNT
FROM CHARGE_ITEM_INFO
GROUP BY REGISTRATION_NO) A
INNER JOIN (SELECT REGISTRATION_NO, SUM(QF_PAMT - QIANFEI) AS QFQI
FROM SHOUFEI_INFO
GROUP BY REGISTRATION_NO) B ON A.REGISTRATION_NO = B.REGISTRATION_NO