获取woo.si_number与stm.serial_number唯一值的SQL查询问题

问题背景 #

需要获取woo.si_number和stm.serial_number的唯一值组合，移除stock表关联时能得到唯一的si_number，但关联该表后出现大量重复行，需同时保留两个字段的唯一结果。

原SQL代码 #

select woo.si_number, pnm.pn,cmp.company_name, wwt.description, stm.serial_number
from WO_OPERATION woo join parts_master pnm on woo.pnm_auto_key = pnm.pnm_auto_key
                      join companies cmp on cmp.cmp_auto_key = woo.cmp_auto_key
                      join wo_work_type wwt on wwt.wwt_auto_key = woo.wwt_auto_key
                      join stock stm on stm.pnm_auto_key = pnm.pnm_auto_key
where pnm.pn = '5A3307-7' AND
      stm.serial_number like 'BNG%' AND
      wwt.description = 'OVERHAULED'
order by woo.si_number

实际输出 #

期望输出 #

问题原因 #

关联stock表时，同一个pnm_auto_key对应多条库存记录，与主表数据形成笛卡尔积，导致重复行出现。

解决方案 #

方法1：使用DISTINCT过滤重复组合 #

精简SELECT字段，添加DISTINCT关键字直接去重：

select distinct woo.si_number, pnm.pn, stm.serial_number
from WO_OPERATION woo join parts_master pnm on woo.pnm_auto_key = pnm.pnm_auto_key
                      join companies cmp on cmp.cmp_auto_key = woo.cmp_auto_key
                      join wo_work_type wwt on wwt.wwt_auto_key = woo.wwt_auto_key
                      join stock stm on stm.pnm_auto_key = pnm.pnm_auto_key
where pnm.pn = '5A3307-7' AND
      stm.serial_number like 'BNG%' AND
      wwt.description = 'OVERHAULED'
order by woo.si_number

如果仅需特定serial_number（如BNG0776），可在WHERE条件中追加stm.serial_number = 'BNG0776'精准过滤。

方法2：使用GROUP BY分组去重 #

通过分组聚合唯一字段组合，适用于需同步进行聚合计算的场景：

select woo.si_number, pnm.pn, stm.serial_number
from WO_OPERATION woo join parts_master pnm on woo.pnm_auto_key = pnm.pnm_auto_key
                      join companies cmp on cmp.cmp_auto_key = woo.cmp_auto_key
                      join wo_work_type wwt on wwt.wwt_auto_key = woo.wwt_auto_key
                      join stock stm on stm.pnm_auto_key = pnm.pnm_auto_key
where pnm.pn = '5A3307-7' AND
      stm.serial_number like 'BNG%' AND
      wwt.description = 'OVERHAULED'
group by woo.si_number, pnm.pn, stm.serial_number
order by woo.si_number

注：若数据库开启ONLY_FULL_GROUP_BY模式，该方式与DISTINCT效果一致。

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