如何合并两个仅细微差异的同表统计SQL查询？已尝试WITH/UNION未果

嘿，我来帮你搞定这个SQL合并的问题！你的两个查询核心差异就在于driver表中callsign的匹配规则，一个是not like '0%'（对应"Not Rental"），另一个是like '0%'（对应"Rental"），其余过滤条件完全一致。完全不用分开写两次查询，下面给你几种更高效的合并方式：

方案1：用CASE分组统计（推荐，性能最优） #

这种方式只需要扫描一次h_driver_balance表，通过JOIN关联driver表后，用CASE语句直接区分两类数据并分组求和，代码更简洁，性能更好：

SELECT
  CASE
    WHEN d.callsign LIKE '0%' THEN 'Rental'
    ELSE 'Not Rental'
  END AS category,
  SUM(h.balance_delta) AS total_balance_delta
FROM h_driver_balance h
JOIN driver d ON h.driver_balance_id = d.id
WHERE h.changed BETWEEN CURRENT_DATE - INTERVAL '1 days' AND CURRENT_DATE
  AND d.driver_ds_account_id = 16
  AND h.comment LIKE 'Refill on the terminal 1%'
GROUP BY category;

方案2：输出为同一行的两列（和原查询格式更匹配） #

如果你希望结果和原来两个查询一样，是两列分别展示"Not Rental"和"Rental"的总和，可以用嵌套CASE的SUM函数实现：

SELECT
  SUM(CASE WHEN d.callsign NOT LIKE '0%' THEN h.balance_delta ELSE 0 END) AS "Not Rental",
  SUM(CASE WHEN d.callsign LIKE '0%' THEN h.balance_delta ELSE 0 END) AS "Rental"
FROM h_driver_balance h
JOIN driver d ON h.driver_balance_id = d.id
WHERE h.changed BETWEEN CURRENT_DATE - INTERVAL '1 days' AND CURRENT_DATE
  AND d.driver_ds_account_id = 16
  AND h.comment LIKE 'Refill on the terminal 1%';

方案3：用WITH + UNION ALL实现（解决你之前的尝试问题） #

如果你坚持想用WITH和UNION的方式，需要注意两个查询的列结构要一致，并且加上分类标识来区分结果。这里用WITH提取公共的driver过滤逻辑，避免重复代码：

WITH filtered_drivers AS (
  SELECT id, callsign
  FROM driver
  WHERE driver_ds_account_id = 16
)
SELECT 'Not Rental' AS category, SUM(balance_delta) AS total
FROM h_driver_balance
WHERE changed BETWEEN CURRENT_DATE - INTERVAL '1 days' AND CURRENT_DATE
  AND driver_balance_id IN (SELECT id FROM filtered_drivers WHERE callsign NOT LIKE '0%')
  AND comment LIKE 'Refill on the terminal 1%'
UNION ALL
SELECT 'Rental' AS category, SUM(balance_delta) AS total
FROM h_driver_balance
WHERE changed BETWEEN CURRENT_DATE - INTERVAL '1 days' AND CURRENT_DATE
  AND driver_balance_id IN (SELECT id FROM filtered_drivers WHERE callsign LIKE '0%')
  AND comment LIKE 'Refill on the terminal 1%';

你之前用WITH和UNION可能没注意到要统一列结构，或者没加分类标识，导致结果不符合预期。另外推荐用UNION ALL而不是UNION，因为这两个查询的结果不会有重复，UNION ALL不需要去重，性能更高。

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