在查询数据时. 写了一段sql 很奇怪.查询前5个表的数据时,不会出现重复数据
直到关联第六个表时(T_IM_InventoryBalance ),重复数据很多。一时间很郁闷.随后咨询实施人员才知。
原来T_IM_InventoryBalance 本身就有多条重复数据,也就是说当material.fid = ib.fmaterialId 关联时
是一对多的关系..需要设置多个条件才能避免重复.
select material.fid from T_IM_StockTransferBill stb
inner join T_IM_StockTransferBillEntry stbe on stb.fid= stbe.fparentId
inner join t_bd_material material on material.fid = stbe.fmaterialId
inner join T_BD_MaterialCompanyInfo mci on material.fid = mci.FMaterialID
inner join T_DB_WAREHOUSE warehouse on stbe.FIssueWarehouseID = warehouse.fid
inner join T_IM_InventoryBalance ib on material.fid = ib.fmaterialId
and ib.FWarehouseID = warehouse.fid and。。。。。
inner join T_IM_STORESTATE storestate on ib.FStoreStatusID = storestate.fid
inner join T_ORG_Storage storage on ib.FStorageOrgUnitID = storage.fid
and fbizdate >=(select fbeginDate from T_BD_Period where fnumber ='200910')
and fbizDate <=(select fendDate from T_BD_Period where fnumber ='200910')
and warehouse.fnumber = 'K11'
and stb.FBaseStatus = 7
and ib.FYear = 2009 and ib.FPeriod = 10
and stb.FIssueStorageOrgUnitID = '4ueEzZGDSYuVfAD/hLHijsznrtQ='
and stb.FReceiptStorageOrgUnitID
in ('eWbCBD/ZQ1+ebRrZDuxF3cznrtQ=','AcgiAdKIT7Cs4dP40U7KTcznrtQ=',
'nXnw4GGNRh+AxZ/cMEMFpcznrtQ=','8vJD+kZuRLuQlkF2ysZ3b8znrtQ=',
'7b2aymBESKGHPqv8xIKs3cznrtQ=')