如何合并两个带不同WHERE条件的SELECT查询并按用户NIP分组？

云上老周

2026-10-5

解决方案

通用兼容方案（支持所有主流数据库）

优先推荐使用UNION ALL合并两个查询结果后二次聚合的方案，兼容性最强，MySQL、PostgreSQL、SQL Server等所有数据库都可正常运行：

SELECT 
  user_nip,
  SUM(total_terima) AS total_terima,
  SUM(total_menunggu) AS total_menunggu
FROM (
  -- 合并第一个统计接收量的查询结果
  SELECT 
    user_pemilik_nip AS user_nip,
    FLOOR(COUNT(id) / 2) AS total_terima,
    0 AS total_menunggu
  FROM data_berkas 
  WHERE status = 'terima' 
  GROUP BY user_pemilik_nip

  UNION ALL

  -- 合并第二个统计待办量的查询结果
  SELECT 
    user_penerima_nip AS user_nip,
    0 AS total_terima,
    FLOOR(COUNT(id) / 2) AS total_menunggu
  FROM data_berkas 
  WHERE status = 'menunggu' 
  GROUP BY user_penerima_nip
) AS combined_data
GROUP BY user_nip
ORDER BY user_nip;

全外连接方案（仅支持支持FULL OUTER JOIN的数据库）

如果使用PostgreSQL、Oracle、SQL Server等支持全外连接的数据库，可以用FULL OUTER JOIN关联两个子查询，性能略优于第一种方案：

SELECT 
  COALESCE(t1.user_pemilik_nip, t2.user_penerima_nip) AS user_nip,
  COALESCE(t1.total_terima, 0) AS total_terima,
  COALESCE(t2.total_menunggu, 0) AS total_menunggu
FROM (
  SELECT user_pemilik_nip, FLOOR(COUNT(id) / 2) as total_terima 
  FROM data_berkas 
  WHERE status = 'terima' 
  GROUP BY user_pemilik_nip
) t1
FULL OUTER JOIN (
  SELECT user_penerima_nip, FLOOR(COUNT(id) / 2) as total_menunggu 
  FROM data_berkas 
  WHERE status = 'menunggu' 
  GROUP BY user_penerima_nip
) t2 ON t1.user_pemilik_nip = t2.user_penerima_nip
ORDER BY user_nip;