有3张表,仓库表:sc_tm_warehouse, 客户表sc_tm_dealer_info , 门店表sc_tm_store_info .
每个客户都有一个仓库,每个门店也都有一个仓库。客户和门店不会指向同一个仓库。
仓库表中有dealer_id, store_id 2个外键。
现在要列出仓库信息和仓库关联的客户或门店信息。
请问sql怎么写呢?
select * from sc_tm_warehouse w, sc_tm_dealer_info di, sc_tm_store_info si
if w.sc_tm_dealer_info_id is not null
then w.sc_tm_dealer_info_id = di.sc_tm_dealer_info_id
else if
w.sc_tm_store_info_id is not null
and w.sc_tm_store_info_id = si.sc_tm_store_info_id
每个客户都有一个仓库,每个门店也都有一个仓库。客户和门店不会指向同一个仓库。
仓库表中有dealer_id, store_id 2个外键。
现在要列出仓库信息和仓库关联的客户或门店信息。
请问sql怎么写呢?
select * from sc_tm_warehouse w, sc_tm_dealer_info di, sc_tm_store_info si
if w.sc_tm_dealer_info_id is not null
then w.sc_tm_dealer_info_id = di.sc_tm_dealer_info_id
else if
w.sc_tm_store_info_id is not null
and w.sc_tm_store_info_id = si.sc_tm_store_info_id
select * from sc_tm_warehouse w, sc_tm_dealer_info di, sc_tm_store_info si
if w.sc_tm_dealer_info_id is not null
then w.sc_tm_dealer_info_id = di.sc_tm_dealer_info_id
and di.channel_type = 'DR'else if
w.sc_tm_store_info_id is not null
then w.sc_tm_store_info_id = si.sc_tm_store_info_id
and si.di.channel_type = 'DR'
判断语句写的不对,请指正谢谢了~~
select *
from sc_tm_warehouse w, sc_tm_dealer_info di, sc_tm_store_info si
where w.dealer_id = di.dealer_id(+) and w.store_id = si.store_id(+)
select sc_tm_warehouse.sc_tm_warehouse_id, sc_tm_warehouse.sc_tm_dealer_info_id, sc_tm_warehouse.sc_tm_store_info_id, sc_tm_dealer_info.sc_tm_dealer_info_id, sc_tm_store_info.sc_tm_store_info_id
from sc_tm_warehouse w, sc_tm_dealer_info d, sc_tm_store_info s
where w.sc_tm_dealer_info_id = d.sc_tm_dealer_info_id(+)
or w.sc_tm_store_info_id = s.sc_tm_store_info_id (+);
sc_tm_warehouse.sc_tm_store_info_id,
sc_tm_dealer_info.sc_tm_dealer_info_id, NULL AS sc_tm_store_info_id
FROM sc_tm_warehouse w, sc_tm_dealer_info d
WHERE w.sc_tm_dealer_info_id = d.sc_tm_dealer_info_id
UNION ALL
SELECT sc_tm_warehouse.sc_tm_warehouse_id, sc_tm_warehouse.sc_tm_dealer_info_id,
sc_tm_warehouse.sc_tm_store_info_id, NULL AS sc_tm_dealer_info_id,
sc_tm_store_info.sc_tm_store_info_id
FROM sc_tm_warehouse w, sc_tm_store_info s
WHERE w.sc_tm_store_info_id = s.sc_tm_store_info_id;