我有兩個表表1：MemberTable和表2：PaymentTable，如下：
我想生成一個報告，從這兩個表中獲得聚合的值，作為表3
。我試著用全外聯和交叉聯接，但它沒有作業。 誰能幫助我生成表3的報告的查詢。 我的嘗試 :
Select
month_type,
月。
從事。
sum(金額) as amount_sum,
count(distinct member_id) as 1st_Table_Member_count。
count(distinct member_id) as 2nd_Table_Member_count
from member_table a
full outer join
支付表b
on a.month=b.month
group by 1,2, 3;
uj5u.com熱心網友回復：
我相信這應該可以解決這個問題，
我把計數移到了兩個CTE中，所以它們是相互獨立的，金額之和在第二個表中，因為這是它唯一有意義的地方，在這一點上，把兩個結果連接在一起，應該可以得到所有的答案。WITH table_1 AS (
SELECT
a.月。
b.month_type,
a.從事。
count(distinct a.member_id) as table_1_count
FROM member_table AS a
LEFT JOIN payment_table AS b
ON a.month = b.month AND a.member_id = b.member_id
GROUP BY 1,2,3 >。
), table_2 AS (
SELECT SELECT
月。
month_type,
從事。
sum(金額) as amount_sum,
count(distinct member_id) as table_2_count
FROM payment_table
GROUP BY 1,2,3)
)
SELECT[/span
COALESCE(a.month, b.month) AS month
COALESCE(a.month_type, b.month_type) AS month_type
COALESCE(a.engaged, b.engaged) AS engaged
b.amount_sum。
a.table_1_count as 1st_Table_Member_count。
b.table_1_count as 2nd_Table_Member_count。
FROM member_table AS a
FULL OUTER JOIN pay_table AS b
ON a.month = b.month AND a.member_id = b.member_id
ORDER BY 1,2, 3;
uj5u.com熱心網友回復：
我不是100%確定這些表格的結構--特別是月份列與這些有什么關系。 但是你可能只需要修復JOIN條件：
Select month_type, month, p.engine,
sum(p.amount) as amount_sum。
count(distinct m.member_id) as 1st_Table_Member_count。
count(distinct p.member_id) as 2nd_Table_Member_count
from member_table m full outer join
支付_表p
using (month, member_id)
group by 1, 2, 3。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/331126.html
標籤：
下一篇：使用特殊字符顯示檔案名