oracle union

select t.lot_number from inv.mtl_onhand_quantities_detail t left join mtl_system_items_b mi on t.inventory_item_id = mi.inventory_item_id where mi.segment1 like '101%' and mi.organization_id = 102 group by t.lot_number having count(*)=1 union select t.lot_number||'x' from inv.mtl_onhand_quantities_detail t left join mtl_system_items_b mi on t.inventory_item_id = mi.inventory_item_id where mi.segment1 like '101%' and mi.organization_id = 102 group by t.lot_number having count(*)=2 union select t.lot_number||'y' from inv.mtl_onhand_quantities_detail t left join mtl_system_items_b mi on t.inventory_item_id = mi.inventory_item_id where mi.segment1 like '101%' and mi.organization_id = 102 group by t.lot_number having count(*)=3 union select t.lot_number||'z' from inv.mtl_onhand_quantities_detail t left join mtl_system_items_b mi on t.inventory_item_id = mi.inventory_item_id where mi.segment1 like '101%' and mi.organization_id = 102 group by t.lot_number having count(*)=3

union 去掉重复记录 union all保留重复记录。