select
zb.release_date as 交易日期,
ht.project_name as 项目名称,
zb.release_user_name as 报料人,
replace(replace(zb.receive_send_type,1,'发料'),2,'收料') as 报料类型,
zb.id as 单据号,
if(
zb.receive_send_type=2 and zb1.material_code=1000,
zb1.total_amt,
if(
zb1.material_code=b1.code and b1.type_id=1000,
SUM(zb1.total_amt * b1.convert_ratio),0)) as 钢管
from
oms_material_release as zb
left join bus_ht as ht on zb.project_id=ht.ht_code
left join oms_material_release_item as zb1 on zb.id=zb1.release_id
left join base_material_info as b1 on zb1.material_code=b1.code
where
1=1
and zb.release_date='2021-11-12～2021-11-18'
-- and zb.id='2111141041619'
order by
zb.receive_send_type
因为你有加其他条件
zb.release_date='2021-11-12～2021-11-18'
你这表关系有一对多的吧 所以会出来重复数据