SQL查询如何合并两列值互换的重复分组并汇总计数

解决方案 #

核心逻辑是对每一行的npi1、npi2做排序，统一把较小值放在第一个字段，较大值放在第二个字段，再基于这两个排序后的字段分组计数即可。

适配绝大多数SQL方言的修改后查询（适用PostgreSQL/MySQL 8.0+/SQL Server 2022+等）

SELECT 
  LEAST(v.npi1, v.npi2) AS npi1,
  GREATEST(v.npi1, v.npi2) AS npi2,
  COUNT(*) AS count
FROM hcpc 
CROSS JOIN LATERAL
     (VALUES (f_rend, f_bill), (f_rend, t_rend), (f_rend, t_bill), (f_bill, t_rend), 
     (f_bill, t_bill), (t_rend, t_bill)
     ) v(npi1, npi2)
WHERE 
     npi1 <> npi2
     AND npi1 <> ''
     AND npi2 <> ''
GROUP BY LEAST(v.npi1, v.npi2), GREATEST(v.npi1, v.npi2)

逻辑说明

• LEAST函数返回两个输入参数中更小的值，GREATEST返回更大的值，这样无论原始v.npi1、v.npi2的顺序如何，同一配对的两个值输出的字段顺序是固定的，比如1033543和1295706无论原始顺序如何，最终都会输出npi1=1033543、npi2=1295706
• 分组条件替换为排序后的两个字段，就会把原始顺序相反的配对分到同一组，COUNT(*)直接统计该配对的总出现次数
• 原查询中的DISTINCT是冗余项，已经执行GROUP BY的场景下不需要额外去重，可以直接删除。

不支持LEAST/GREATEST的数据库兼容写法

如果所用数据库不支持以上两个函数，可以用CASE WHEN实现相同逻辑：

SELECT 
  CASE WHEN v.npi1 < v.npi2 THEN v.npi1 ELSE v.npi2 END AS npi1,
  CASE WHEN v.npi1 < v.npi2 THEN v.npi2 ELSE v.npi1 END AS npi2,
  COUNT(*) AS count
FROM hcpc 
CROSS JOIN LATERAL
     (VALUES (f_rend, f_bill), (f_rend, t_rend), (f_rend, t_bill), (f_bill, t_rend), 
     (f_bill, t_bill), (t_rend, t_bill)
     ) v(npi1, npi2)
WHERE 
     npi1 <> npi2
     AND npi1 <> ''
     AND npi2 <> ''
GROUP BY 
  CASE WHEN v.npi1 < v.npi2 THEN v.npi1 ELSE v.npi2 END,
  CASE WHEN v.npi1 < v.npi2 THEN v.npi2 ELSE v.npi1 END

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