mysql - 我想显示来自 2 个或更多查询的所有记录，这些记录也不在左侧查询中

Question

我有这样的疑问

SET @curr_date = '2017-03-23';

SELECT 
curr_week.mid AS MID, 
curr_week.EDC AS Merchant_Name ,
COALESCE(curr_week.amount,0) AS Total_Amount_Curr_Week, 
COALESCE(curr_week.total_trx,0) AS Total_Trx_Curr_Week, 
COALESCE(curr_week.total_user,0) AS Total_User_Curr_Week,

COALESCE(last_week.amount,0) AS Total_Amount_Last_Week,

COALESCE(last_week.total_trx,0) AS Total_Trx_Last_Week,

COALESCE(last_week.total_user,0) AS Total_User_Last_Week
FROM
(
    SELECT a.*, b.total_user
    FROM 
    (
        SELECT a1.owner_name AS MID, m.name AS EDC,SUM(t1.amount) AS amount, COUNT(t1.id) AS total_trx
        FROM members m
        JOIN accounts a1 ON a1.member_id = m.id
        JOIN transfers t1 ON a1.id = t1.to_account_id
        WHERE DATE(t1.DATE) = (@curr_date - INTERVAL 1 DAY)
        GROUP BY a1.owner_name
    ) AS a

    JOIN

    (-- get total user
        SELECT COUNT(r.ecash_no) AS total_user, r.mid, r.merchant_name
        FROM 
            (
                SELECT a.`owner_name` AS ecash_no,
                a1.owner_name AS MID, 
                m.name AS merchant_name 
                FROM accounts a1
                JOIN transfers t1 ON a1.id = t1.to_account_id
                JOIN members m ON a1.member_id = m.id
                JOIN accounts a ON a.id = t1.from_account_id
                WHERE DATE(t1.date) = (@curr_date - INTERVAL 1 DAY)
                GROUP BY a.owner_name,m.`name`
            ) AS r
        GROUP BY r.mid
    ) AS b ON a.mid = b.mid
) AS curr_week

JOIN

(
-- last week
    SELECT a.*, b.total_user
    FROM 
    (
        SELECT a1.owner_name AS MID, m.name AS EDC,SUM(t1.amount) AS amount, COUNT(t1.id) AS total_trx
        FROM members m
        JOIN accounts a1 ON a1.member_id = m.id
        JOIN transfers t1 ON a1.id = t1.to_account_id
        WHERE DATE(t1.DATE) = (@curr_date - INTERVAL 1 DAY) - INTERVAL 1 WEEK
        GROUP BY a1.owner_name
    ) AS a

    JOIN

    (-- get total user
        SELECT COUNT(r.ecash_no) AS total_user, r.mid, r.merchant_name
        FROM (
            SELECT a.`owner_name` AS ecash_no,
            a1.owner_name AS MID, 
            m.name AS merchant_name 
            FROM accounts a1
            JOIN transfers t1 ON a1.id = t1.to_account_id
            JOIN members m ON a1.member_id = m.id
            JOIN accounts a ON a.id = t1.from_account_id
            WHERE DATE(t1.date) = (@curr_date - INTERVAL 1 DAY) - INTERVAL 1 WEEK
            GROUP BY a.owner_name,m.`name`
        ) AS r
        GROUP BY r.mid
    ) AS b ON a.mid = b.mid
) AS last_week ON curr_week.mid = last_week.mid

我如何从这样的连接查询中检索所有 EDC 值。因为如果我使用 join ，它只显示相同的值。如果我使用左连接，它将遵循左查询的值有没有办法通过 join 显示所有内容？

Answer 1

您可以使用以下命令模拟两个表的完整外部联接:

SELECT COALESCE(a.ColA, b.ColA) AS ColA,
       COALESCE(a.ColB, b.ColB) AS ColB
FROM tableA a
LEFT JOIN tableB b ON a.ColA = b.ColA
UNION
SELECT COALESCE(a.ColA, b.ColA) AS ColA,
       COALESCE(a.ColB, b.ColB) AS ColB
FROM tableA a
RIGHT JOIN tableB b ON a.ColA = b.ColA;

注意:我假设只有 ColA 是连接列。您也可以添加 ColB 作为连接列，或仅使用 ColB 作为连接列。这实际上取决于您的表的设计，但我给出的一般方法应该仍然有效。

输出:

此处演示:

Rextester