null帶來的麻煩

-- =============================================================================
-- 標題: null帶來的麻煩
-- 原創: takako_mu
-- 描述: 當數據中出現null時，可能會導致
-- 时间: 2009-10-28
-- 地点: 昆山
-- =============================================================================
/*有兩個表,一個采購明細表,一個庫存表.
采購明細表 TableA:
采购单号    行号   商品     数量 
A00001      100    GOOD1    2000 
A00001      200    GOOD2    4000 
A00001      300    GOOD3    7000 
A00002      100    GOOD1    3000 
A00002      200    GOOD2    3000 

采購庫存表 TableB:
商品   库位   库存 
GOOD1  K01    1000 
GOOD1  K02    2000 
GOOD2  K01    4000 

現要求生成如下Table.
采购单号    行号    商品      数量    需求总量    库存数量    缺货量 
A00001      100    GOOD1    2000    5000        3000        2000 
A00001      200    GOOD2    4000    7000        4000        3000 
A00001      300    GOOD3    7000    7000        0          7000 
A00002      100    GOOD1    3000    5000        3000        2000 
A00002      200    GOOD2    3000    7000        4000        3000 
*/

if object_id('TableA')is not null drop table TableA
go
create table TableA(采购单号 varchar(10), 行号 int,商品 varchar(5), 数量 int)
insert TableA select
'A00001',      100 ,   'GOOD1',    2000 union all select
'A00001',      200 ,   'GOOD2',    4000 union all select
'A00001',      300 ,   'GOOD3',    7000 union all select
'A00002',      100 ,   'GOOD1',    3000 union all select
'A00002',      200 ,   'GOOD2',    3000 

if object_id('TableB')is not null drop table TableB
go
create table TableB( 商品 varchar(5),库位 varchar(5), 库存 int)
insert TableB select 
'GOOD1',  'K01',    1000 union all select
'GOOD1',  'K02',    2000 union all select
'GOOD2',  'K01',    4000 
-----------------------------------------------------------------
--正常情況下我們會這樣寫:雖然運行沒有問題,但一切和null運算后得結果都為null,這顯然不是我們要的結果
SELECT A.*,B.需求总量,C.库存数量 AS 库存数量,B.需求总量-C.库存数量 AS 缺货量
    FROM TableA A 
        LEFT JOIN (SELECT 商品,SUM(数量) AS 需求总量 FROM TableA GROUP BY 商品) B
        ON A.商品=B.商品
        LEFT JOIN (SELECT 商品,SUM(库存) AS 库存数量 FROM TableB GROUP BY 商品)C
        ON A.商品=C.商品

--采购单号       行号          商品    数量          需求总量        库存数量        缺货量
------------ ----------- ----- ----------- ----------- ----------- -----------
--A00001     100         GOOD1 2000        5000        3000        2000
--A00001     200         GOOD2 4000        7000        4000        3000
--A00001     300         GOOD3 7000        7000        NULL        NULL
--A00002     100         GOOD1 3000        5000        3000        2000
--A00002     200         GOOD2 3000        7000        4000        3000

/*事實上解決此類問題的辦法有倆個:
1.可以創建table或者創建臨時表時,就規定這個欄位當為null時就設定為空
2.如果不需要創建table,如這個例子,我們就用isnull來解決這個問題
*/
-----------------------------------------------------------------
--加上isnull判斷后得正確結果如下.
SELECT A.*,B.需求总量,ISNULL(C.库存数量,0) AS 库存数量,B.需求总量-ISNULL(C.库存数量,0) AS 缺货量
    FROM TableA A 
        LEFT JOIN (SELECT 商品,SUM(数量) AS 需求总量 FROM TableA GROUP BY 商品) B
        ON A.商品=B.商品
        LEFT JOIN (SELECT 商品,SUM(库存) AS 库存数量 FROM TableB GROUP BY 商品)C
        ON A.商品=C.商品

--采购单号       行号          商品    数量          需求总量        库存数量        缺货量
------------ ----------- ----- ----------- ----------- ----------- -----------
--A00001     100         GOOD1 2000        5000        3000        2000
--A00001     200         GOOD2 4000        7000        4000        3000
--A00001     300         GOOD3 7000        7000        0           7000
--A00002     100         GOOD1 3000        5000        3000        2000
--A00002     200         GOOD2 3000        7000        4000        3000