求一条sql查询语句

Question

现有两个表:
t1(user_mobile,total_score...);
t2(user_mobile,trans_counts...);现要查询出每个user_mobile的total_score，sum(trans_counts),其中有部分号码只存在于t1表而不在t2表中，不在t2表中
user_mobile的sum(trans_counts)显示为空或者0。

Answer 1

select user_mobile,sum(total_score) total_score,sum(trans_counts) trans_counts
from(
select t1.user_mobile,sum(t1.total_score) total_score,0 trans_counts from t1 group by t1.user_mobile
union all
select t2.user_mobile,0,sum(t2.trans_counts) trans_counts from t2 group by t2.user_mobile
) group by user_mobile

Answer 2

select decode(total_score,null,0,total_score) total_score,sum(trans_counts) trans_counts from t1 left join t2 on t1.user_mobile=t2.user_mobile

Answer 3

SELECT
  T1.total_score,
  DECODE(T3.sum_trans_counts,NULL,0,T3.sum_trans_counts)
FROM
  T1,
  (SELECT 
     T2.user_mobile,
     SUM(T2.trans_counts)
   FROM
     T2
   GROUP BY T2.user_mobile) T3
WHERE
  T1.user_mobile = T3.user_mobile(+)

Answer 4

select t1.user_mobile,
       nvl(sum_counts,0) as 
  from t1,
       (
        select t2.user_mobile,
               sum(t2.trans_counts) as sum_counts
          from t2 
        group by t2.user_mobile
       )tt
 where t1.user_mobile = tt.user_mobile(+);

Answer 5

请问这样是否正确：
  
Select T1.user_mobile,sum(T1.total_score),
DECODE(sum(T2.trans_counts),NULL,0,sumT2.trans_counts)) 
FROM  T1, T2
Where T1.user_mobile = T2.user_mobile(+)
Group By T1.user_mobile,T1.total_score;这样结果也是一样的，那不是更加简单？

Answer 6

既然t1表是全量，直接用一个外连接就可以了呀
Select T1.user_mobile,sum(T1.total_score), sum(nvl(T2.trans_counts,0))  
FROM  T1, T2 
Where T1.user_mobile = T2.user_mobile(+) 
Group By T1.user_mobile; 

Answer 7

select t1.user_mobile,t1.total_score , t3.trans_counts from t1 full join (select user_mobile ,sum(trans_counts) as trans_counts from t2 group by user_mobile  )t3
on t1.user_mobile =t3.user_mobile

Answer 8

t1、t2表结构是怎样的？假设t1中user_mobile是主键，t2中user_mobile不是主键：
SELECT T1.USER_MOBILE,T1.TOTAL_SCORE,DECODE(T3.TRANS_COUNTS,0) 
   FROM T1,
        (SELECT USER_MOBILE,SUM(TRANS_COUNTS) AS TRANS_COUNTS FROM T2 GROUP BY USER_MOBILE) T3 
 WHERE T1.USER_MOBILE=T3.USER_MOBILE(+)