我先有两张桌子 A 有 column id,phone_number,refer_amount
和第二个 B 有列 phone_number,transaction_amount
现在我想要使用按 phone_number 分组而不使用内部查询的两个表中sum()
的 refer_amount 和 transaction_amount 以及count()
phone_number
表A
电话号码refer_amount 123 50 456 80 789 90 123 90 123 80 123 20 456 20 456 79 456 49 123 49
表B
PHONE_NUMBER TRANSACTION_AMOUNT 123 50 123 51 123 79 456 22 456 11 456 78 456 66 456 88 456 88 456 66 789 66 789 23 789 78 789 46
我试过以下查询,但它给了我错误的输出:
SELECT a.phone_number,COUNT(a.phone_number) AS refer_count,SUM(a.refer_amount) AS refer_amount,b.phone_number,COUNT(b.phone_number) AS toal_count,SUM(b.transaction_amount) AS transaction_amount FROM dbo.A AS a,dbo.B AS b WHERE a.phone_number=b.phone_number GROUP BY a.phone_number,b.phone_number
输出(错误):
phone_number refer_count refer_amount phone_number transaction_count transaction_amount 123 15 867 123 15 900 456 28 1596 456 28 1676 789 5 450 789 5 291
输出(我想要的):
phone_number refer_count refer_amount phone_number transaction_count transaction_amount 123 5 289 123 3 180 456 4 228 456 7 419 789 1 90 789 5 291
我会B
在单独的子查询中对表进行聚合,然后加入它:
SELECT
a.phone_number,
COUNT(a.phone_number) AS a_cnt,
SUM(a.refer_amount) AS a_sum,
COALESCE(b.b_cnt, 0) AS b_cnt,
COALESCE(b.b_sum, 0) AS b_sum
FROM A a
LEFT JOIN
(
SELECT
phone_number,
COUNT(*) AS b_cnt,
SUM(transaction_amount) AS b_sum
FROM B
GROUP BY phone_number
) b
ON a.phone_number = b.phone_number;
您当前方法的一个主要潜在问题是连接可能导致重复计数,因为表中的给定phone_number
记录A
由于连接而被复制。
说到连接,请注意上面我使用了显式连接,而不是您使用的隐式连接。一般来说,你不应该在FROM
子句中加入逗号。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句