SQL执行INNER JOIN后GROUP BY分组求和结果不符合预期如何解决

问题根因 #

原查询仅用id字段关联两张表，会导致同id下的user_raw每条记录和user_details所有记录产生笛卡尔积匹配，没有校验user_raw.detail中是否存在对应的键值对，因此求和结果不符合预期。

正确SQL语句 #

select 
  r.id,
  d.key,
  d.value,
  sum(r.amount) as sum
from user_raw as r 
inner join user_details d
  on d.id = r.id
  -- 新增键值匹配的关联条件
  and r.detail ? d.key 
  and r.detail ->> d.key = d.value
group by 1, 2, 3;

注：上述语句适配PostgreSQL语法，其中?是JSONB类型判断是否包含指定键的专用操作符，->>用于提取JSONB字段中指定键的文本值。

执行结果 #

执行后得到的统计结果符合逻辑：

你给出的预期中job=police的sum值714为笔误，实际238+486的正确计算结果为724。

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