UNION 和 UNION ALL 的使用来简化编程工作

瞄一眼需求：

　　有商品表 product，仓库表warehouse，库存表stockdet，

 　　product

id_product	productname
1	product1
2	product2
3	product3
4	product4

 

　　warehouse

id_warehouse	warehousename
1	warehouse1
2	warehouse2
3	warehouse3

 

　　stockdet

id_stockdet	id_product	id_warehouse	stockcnt
1	1	2	20
2	3	3	60

 

　　要得到的结果是：做个列表，展示出所有 仓库+商品 维度的库存数据，

　　

需求分析：

　　在库存表stockdet 只有 1号商品，2号仓库库存20个；3号商品，3号仓库库存60个；

　　比如：像1号商品1号仓库的库存，在库存表stockdet是没有的记录的，但是需求是要展示的，

　　怎么做？

　　用一条SQL来查出这个列表

 

再看看UNION 和 UNION ALL的用法， 传送门。

 

解决方案：

select 
pd.id_product,wh.id_warehouse,pd.productname,wh.warehousename,std.stockcnt 
from stockdet std
LEFT JOIN product pd on std.id_product=pd.id_product
LEFT JOIN warehouse wh on std.id_warehouse=wh.id_warehouse

UNION ALL
SELECT pd.id_product,"1" as id_warehouse,pd.productname,"warehouse1" as warehousename,"0" as stockcnt 
from product pd 
where pd.id_product not in(SELECT DISTINCT id_product from stockdet WHERE id_warehouse=1)

UNION ALL
SELECT pd.id_product,"2" as id_warehouse,pd.productname,"warehouse2" as warehousename,"0" as stockcnt 
from product pd 
where pd.id_product not in(SELECT DISTINCT id_product from stockdet WHERE id_warehouse=2)

UNION ALL
SELECT pd.id_product,"3" as id_warehouse,pd.productname,"warehouse3" as warehousename,"0" as stockcnt 
from product pd 
where pd.id_product not in(SELECT DISTINCT id_product from stockdet WHERE id_warehouse=3)

ORDER BY id_product,id_warehouse ASC

 

表结构下载