收费项目表(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 表查询 急急急急急

解决方案 »

  1.   

    SELECT a.registration_no,a.paid_up,a.discount,b.qf_pamt-b.qianfei FROM charge_item_info a,
    (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!
      

  2.   

    BTW, below is the result:
    REGISTRATION_NO                                                 PAID_UP   DISCOUNT    Rest_mn
    ------------------------------------------------------------ ---------- ---------- ----------
    A003                                                                300         20          0
    A001                                                               1100        100          0
    A002                                                                120          0        100
      

  3.   

    应该这样处理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,yeE) b  ON B.REGISTRATION_NO =A.REGISTRATION_NO
    一起研究oracle,qq:34246450   有事常联系!
      

  4.   

    错了,应该这个
    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
      

  5.   

    SELECT A.REGISTRATION_NO, A.PAID_UP, A.DISCOUNT, B.QFQI
      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