如何将两个SQL日期范围的查询结果合并到同一输出中

问题原因 #

你编写的子查询没有关联外层查询的inv_account字段，导致子查询每次都统计全表对应日期范围的总消费额，而非分组后单个账户的对应金额。

修正方案1：关联子查询（适配你原有逻辑） #

在子查询中新增和外层账户的关联条件即可：

SELECT 
    inv_account,
    (SELECT SUM(basevalue) 
     FROM salestrans s2 
     WHERE s2.inv_account = s1.inv_account 
       AND s2.inv_invoicedate BETWEEN '2019-04-01' AND '2020-03-31') AS fy2019_amount,
    (SELECT SUM(basevalue) 
     FROM salestrans s2 
     WHERE s2.inv_account = s1.inv_account 
       AND s2.inv_invoicedate BETWEEN '2020-04-01' AND '2021-03-31') AS fy2020_amount
FROM salestrans s1
GROUP BY inv_account;

修正方案2：条件聚合（更推荐，性能更优） #

不需要嵌套子查询，仅需扫描一次表，通过CASE WHEN在聚合时过滤对应日期的记录即可：

SELECT 
    inv_account,
    SUM(CASE WHEN inv_invoicedate BETWEEN '2019-04-01' AND '2020-03-31' THEN basevalue ELSE 0 END) AS fy2019_amount,
    SUM(CASE WHEN inv_invoicedate BETWEEN '2020-04-01' AND '2021-03-31' THEN basevalue ELSE 0 END) AS fy2020_amount
FROM salestrans
GROUP BY inv_account;

如果需要过滤掉两个财年都无消费的账户，可在语句末尾添加HAVING fy2019_amount > 0 OR fy2020_amount > 0。

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