請教高手:合並的問題:在線等,急!!!!!

[zjllxy]

題目如下:
有兩張通過sql語句得到的數據表
表1數據如下:
ShoppingListNo ShoppingSeq purQty price Accountingtitle
PIAK40920001 01 800 .63 114110
PIAK40920001 02 650 .69 114110
PIAK40920007 03 11500 .74 114110
PIAK40920007 04 2650 .81 114110
PIAK40920007 05 44900 .80 114110
PIAK40920007 06 9000 .89 114110

表2的數據得到后如下:
ShoppingListNo ShoppingSeq SumQty purQty
PIAK40920007 03 900.00 11500
PIAK40920007 04 378.00 2650
PIAK40920007 05 800.00 44900
PIAK40920007 06 3250.00 9000

過程描述:
表2的SumQty通過以前驗收過的(同一采購單號+批次)后得到的匯總數量,是從驗收表中得到的數據,條件也是通過ShoppingListNo+ShoppingSeq條件從表1中的6張單號得到的,因為在驗收表采購單PIAK40920001還沒有驗收.所以就沒有查詢出來,
表1是由那6張采購單號+批次得到總共要采購的數量和單價,

而我要的結果是表2的中的數據如下
ShoppingListNo ShoppingSeq SumQty purQty
PIAK40920001 01 0.00 800
PIAK40920001 02 0 650
PIAK40920007 03 900.00 11500
PIAK40920007 04 378.00 2650
PIAK40920007 05 800.00 44900
PIAK40920007 06 3250.00 9000

可是老是得不到想要的結果(老是PIAK40920001的兩項顯示不出來 )原因是在驗收表中還沒有驗收過!
當然如果一條的更新用cursor的方式可以做到,以前做倉庫的時候用過那樣,感覺不好!
不知可否用sql語句得到!

[zjcxc]

用 left join 就行了嘛

要么数据,要么帖你的查询语句出来

[张海霖]

帮顶，

[zjllxy]

得到表1的語句如下:
select b.ShoppingListNo,b.MaterialUntN,b.ExpectWeight,b.MaterialName,b.SpecName,
B.ShoppingSeq,b.PurQty,b.Price,a.AccountingTitle
from Pur_ShoppingList a join Pur_ShoppingDetailList b on
a.ShoppingListNo=b.ShoppingListNo
where b.ShoppingSeq='01' and b.ShoppingListNo='PIAK40920001'
or b.ShoppingSeq='02' and b.ShoppingListNo='PIAK40920001'
or b.ShoppingSeq='03' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='06' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='05' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='04' and b.ShoppingListNo='PIAK40920007 '

得到表2的sql語句如下:
select b.ShoppingListNo,b.ShoppingSeq,b.PurQty,
Sum(a.CheckOkQty) as Qty,b.Price,b.AccountingTitle
from (select a.ShoppingListNo,b.PurQty,b.Price,a.AccountingTitle,b.ShoppingSeq
from Pur_ShoppingList a join Pur_ShoppingDetailList b on
a.ShoppingListNo=b.ShoppingListNo
where b.ShoppingSeq='01' and b.ShoppingListNo='PIAK40920001'
or b.ShoppingSeq='02' and b.ShoppingListNo='PIAK40920001'
or b.ShoppingSeq='03' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='06' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='05' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='04' and b.ShoppingListNo='PIAK40920007 ' ) b
join Pur_ReceiptDetailList a on a.ShoppingSeq=b.ShoppingSeq and a.ShoppingListNo=b.ShoppingListNo
Group by b.ShoppingListNo,b.ShoppingSeq,b.PurQty,b.Price,b.AccountingTitle

可是在表Pur_ReceiptDetailList中(驗收表)暫時沒有PIAK40920001的資料,所以得到的資料只有上面
表2的4行資料能不能得到我想要的結果
ShoppingListNo ShoppingSeq SumQty purQty
PIAK40920001 01 0.00 800
PIAK40920001 02 0 650
PIAK40920007 03 900.00 11500
PIAK40920007 04 378.00 2650
PIAK40920007 05 800.00 44900
PIAK40920007 06 3250.00 9000

請高手指點一二!

[zjcxc]

select b.ShoppingListNo,b.ShoppingSeq,b.PurQty,
Sum(a.CheckOkQty) as Qty,b.Price,b.AccountingTitle
from(
select a.ShoppingListNo,b.PurQty,b.Price,a.AccountingTitle,b.ShoppingSeq
from Pur_ShoppingList a
join Pur_ShoppingDetailList b on a.ShoppingListNo=b.ShoppingListNo
where b.ShoppingSeq='01' and b.ShoppingListNo='PIAK40920001'
or b.ShoppingSeq='02' and b.ShoppingListNo='PIAK40920001'
or b.ShoppingSeq='03' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='06' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='05' and b.ShoppingListNo='PIAK40920007'
or b.ShoppingSeq='04' and b.ShoppingListNo='PIAK40920007 '
) b left join Pur_ReceiptDetailList a on a.ShoppingSeq=b.ShoppingSeq and a.ShoppingListNo=b.ShoppingListNo
Group by b.ShoppingListNo,b.ShoppingSeq,b.PurQty,b.Price,b.AccountingTitle

[张海霖]

老大我真佩服你，真是诲人不倦。

[tinghuyang]

up