如何基于不同WHERE子句从表选多列并合并多个SQL查询为单查询

方案一：条件聚合+子查询（高效简洁） #

直接在单条查询中整合所有需求，仅扫描PAYMENT表一次，效率更高：

SELECT
    COALESCE(SUM(AMOUNT), 0) AS TOTAL_AMOUNT,
    COALESCE(SUM(CASE WHEN STATE = 0 THEN AMOUNT ELSE 0 END), 0) AS NOT_SETTLED_AMOUNT,
    COALESCE(SUM(CASE WHEN STATE = 1 THEN AMOUNT ELSE 0 END), 0) AS SETTLED_AMOUNT,
    (SELECT CREATION_DATE FROM PAYMENT WHERE PAYMENT_ID = (SELECT MAX(PAYMENT_ID) FROM PAYMENT)) AS LATEST_PAYMENT_DATE,
    (SELECT MAX(ID) FROM INQUIRY) AS MAX_INQUIRY_ID
FROM PAYMENT;

逻辑说明：

• 总金额/分状态金额：用CASE WHEN实现条件聚合，只对符合状态的AMOUNT求和；COALESCE用来处理表为空时返回0而非NULL的情况（可根据业务需求移除）。
• 最新付款日期：保留原查询的子查询逻辑，直接作为列返回。
• INQUIRY表最大ID：通过子查询获取该值，作为独立列输出。

方案二：子查询交叉连接（逻辑清晰） #

如果希望拆分不同表的查询逻辑，用交叉连接的方式更易维护：

SELECT
    pay_stats.TOTAL_AMOUNT,
    pay_stats.NOT_SETTLED_AMOUNT,
    pay_stats.SETTLED_AMOUNT,
    pay_stats.LATEST_PAYMENT_DATE,
    inquiry_stats.MAX_INQUIRY_ID
FROM (
    SELECT
        COALESCE(SUM(AMOUNT), 0) AS TOTAL_AMOUNT,
        COALESCE(SUM(CASE WHEN STATE = 0 THEN AMOUNT ELSE 0 END), 0) AS NOT_SETTLED_AMOUNT,
        COALESCE(SUM(CASE WHEN STATE = 1 THEN AMOUNT ELSE 0 END), 0) AS SETTLED_AMOUNT,
        (SELECT CREATION_DATE FROM PAYMENT WHERE PAYMENT_ID = (SELECT MAX(PAYMENT_ID) FROM PAYMENT)) AS LATEST_PAYMENT_DATE
    FROM PAYMENT
) pay_stats
CROSS JOIN (
    SELECT MAX(ID) AS MAX_INQUIRY_ID FROM INQUIRY
) inquiry_stats;

这种方式将PAYMENT的统计和INQUIRY的查询拆分为两个独立子查询，再通过CROSS JOIN合并结果，逻辑更直观，适合复杂场景下的后续维护。

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