如何在MySQL中关联无匹配条件的表？订单与支付表日结查询问题

要同时获取指定日期的订单（含未付款赊账订单）和支付记录（含无订单预付款），需解决原查询仅从订单表出发导致无订单时无结果的问题，以下是两种实用方案：

方案1：全外连接（适配支持该语法的数据库，如MySQL 8.0+、PostgreSQL等） #

先分别筛选指定日期的订单和支付汇总数据，再通过日期关联，确保两种场景都被覆盖：

SELECT
    o.id,
    o.order_date,
    o.customer,
    o.inv_number,
    p.pymt,
    p.cash,
    p.bank
FROM
    (SELECT * FROM orders WHERE order_date = '2022/10/25') o
FULL OUTER JOIN
    (SELECT
         pymt_date,
         SUM(amount) AS pymt,
         SUM(cash) AS cash,
         SUM(bank) AS bank
     FROM payments
     WHERE pymt_date = '2022/10/25'
     GROUP BY pymt_date) p
    ON o.order_date = p.pymt_date;

如果需要保留订单和支付的明细而非汇总，可调整为：

SELECT
    o.id,
    o.order_date,
    o.customer,
    o.inv_number,
    p.id AS payment_id,
    p.amount AS pymt,
    p.cash,
    p.bank
FROM
    orders o
FULL OUTER JOIN
    payments p
    ON o.id = p.order_id 
    AND COALESCE(o.order_date, p.pymt_date) = '2022/10/25'
WHERE
    o.order_date = '2022/10/25' 
    OR p.pymt_date = '2022/10/25';

方案2：UNION ALL合并结果（兼容所有数据库） #

若数据库不支持全外连接，可分别查询订单和支付数据后合并，灵活适配明细或汇总需求：

明细级合并（保留每条订单/支付记录） #

-- 提取指定日期的所有订单
SELECT
    id,
    order_date,
    customer,
    inv_number,
    NULL AS payment_id,
    NULL AS pymt,
    NULL AS cash,
    NULL AS bank,
    '订单' AS record_type
FROM orders
WHERE order_date = '2022/10/25'

UNION ALL

-- 提取指定日期的所有支付记录
SELECT
    NULL AS id,
    pymt_date AS order_date,
    NULL AS customer,
    NULL AS inv_number,
    id AS payment_id,
    amount AS pymt,
    cash,
    bank,
    '支付' AS record_type
FROM payments
WHERE pymt_date = '2022/10/25';

汇总级合并（生成日结汇总数据） #

-- 订单日期汇总
SELECT
    order_date,
    COUNT(id) AS order_count,
    SUM(total_amount) AS total_order_amount, -- 按需替换为订单表实际金额字段
    NULL AS total_payment,
    NULL AS total_cash,
    NULL AS total_bank
FROM orders
WHERE order_date = '2022/10/25'
GROUP BY order_date

UNION ALL

-- 支付日期汇总
SELECT
    pymt_date AS order_date,
    NULL AS order_count,
    NULL AS total_order_amount,
    SUM(amount) AS total_payment,
    SUM(cash) AS total_cash,
    SUM(bank) AS total_bank
FROM payments
WHERE pymt_date = '2022/10/25'
GROUP BY pymt_date;

原查询问题分析 #

你的原查询以orders表为左连接主表，且通过WHERE o.order_date = '2022/10/25'过滤结果——当指定日期无订单时，orders表无返回数据，左连接后自然无法得到当天的支付记录。必须提前筛选各表的指定日期数据，或改用全外连接/UNION逻辑覆盖两种场景。

内容的提问来源于stack exchange，提问作者raj