-- =============================================================================
-- 標題: 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
                    
                
                
            
        
浙公网安备 33010602011771号