TRY IT AGAIN..
SQL> SELECT UL.ACCOUNTS,
2 COUNT(1) "USERS",
3 SUM(NVL(RENT,0.00)) "RENT",
4 SUM(NVL(FEE01,0.00)) "FEE01",
5 SUM(NVL(FEE02,0.00)) "FEE02",
6 SUM(NVL(FEE03,0.00)) "FEE03",
7 SUM(NVL(FEE04,0.00)) "FEE04"
8 FROM USERLIST UL,
9 CHARGE CH
10 WHERE UL.TELEPHONE = CH.TELEPHONE(+)
11 GROUP BY UL.ACCOUNTS;ACCOUNTS USERS RENT FEE01 FEE02 FEE03 FEE04
-------- ---------- ---------- ---------- ---------- ---------- ----------
AAAA 2 40 32 34 36 38
BBBB 1 100 31 32 33 34
CCCC 1 250 0 0 0 0SQL>
SQL> SELECT UL.ACCOUNTS,
2 COUNT(1) "USERS",
3 SUM(NVL(RENT,0.00)) "RENT",
4 SUM(NVL(FEE01,0.00)) "FEE01",
5 SUM(NVL(FEE02,0.00)) "FEE02",
6 SUM(NVL(FEE03,0.00)) "FEE03",
7 SUM(NVL(FEE04,0.00)) "FEE04"
8 FROM USERLIST UL,
9 CHARGE CH
10 WHERE UL.TELEPHONE = CH.TELEPHONE(+)
11 GROUP BY UL.ACCOUNTS;ACCOUNTS USERS RENT FEE01 FEE02 FEE03 FEE04
-------- ---------- ---------- ---------- ---------- ---------- ----------
AAAA 2 40 32 34 36 38
BBBB 1 100 31 32 33 34
CCCC 1 250 0 0 0 0SQL>
表Charge
左连
表UserList
即可以得到要的数据!
SELECT '4210001' TELEPHONE,'AAAA' ACOUNTS,19.50 RENT FROM DUAL
UNION ALL
SELECT '4210002' TELEPHONE,'AAAA' ACOUNTS,20.50 RENT FROM DUAL
......