把详细表多行合并成一行,
(select 单据号,物料编码 from test_tablemx order by 单据号,物料编码) t1
(select t1.单据号,wm_concat(t1.物料编码) 明细商品 from t1 group by t1.单据号 ) t2
关联主从表
(select t0.客户,t0.单据号,t2.明细商品 from test_table t0,t2 where t0.单据号=t2.单据号) t3
匹配两数据
(select B.客户,B.单据号,B.明细商品 from t3 as A ,t3 as B where A.客户=B.客户 and A.明细商品=B.明细商品 and A.单据号<>B.单据号) C
以上数据如果有3条以上的重复数据,就会造成单据号的重复显示。
过滤
select C.客户,C.单据号,C.明细商品 from C group by C.客户,C.单据号,C.明细商品