如何新建這樣的視圖(View) 在線待

[lliang0488]

現有三個視圖(己建好)，要將這三個視圖再組成一個視圖
PO : IDS (int), itemno,itemname,pono
InQty: Code(int), SumInQty (收貨數)(指一張PO收貨總數)
OutQty:Code(int), SumOutQty (發貨數)(指一張PO發貨總數)

一筆 PO 記錄，匹配一筆 InQty、OutQty (都是通過 IDS = Code ) 來連接，當一張 PO 都有收發記錄時即好辦(通過 Inner Join 連接)，但當一張PO 有收貨沒有發貨或有發貨沒有收貨時就無法顯示其中只有收或發的記錄，如下：

無發貨記錄時：A
PO InQty OutQty
IDS itemno itemname pono code SumInQty code SumOutQty
100 #3372 Splish Food PO-0506-001 100 1200 Null null

無收貨記錄時：B
PO InQty OutQty
IDS itemno itemname pono code SumInQty code SumOutQty
101 #3373 Sky Tools PO-0506-002 null null 101 2000

象上面這樣的情況時則通過內聯(Inner Join on) 來連接時則無法顯示出記錄，現在想達到的效果時，不管
InQty 或 OutQty 的情況如何，只要有匹配的記錄就顯示出來，比如A 顯示成
100 #3372 Splish Food PO-0506-001 100 1200 Null null
B 顯示成
101 #3373 Sky Tools PO-0506-002 null null 101 2000

謝謝各位大師指點，最好能給出詳細結構或代碼

[paoluo]

Create View List
As
Select
A.*,
B.SumInQty,
C.SumOutQty
From PO A
Left Join InQty B
On A.IDS=B.Code
Left Join OutQty C
On A.IDS=C.Code
Where A.IDS In
(Select Code From InQty
Union
Select Code From OutQty)
GO

[昵称被占用了]

Create View List
As
Select
A.*,
B.SumInQty,
C.SumOutQty
From PO A
Left Join InQty B
On A.IDS=B.Code
Left Join OutQty C
On A.IDS=C.Code
Where b.Code is not null or c.Code is not null
GO

[Reeezak]

SELECT A.*, B.*, C.* FROM
@OUTQTY AS C
RIGHT JOIN
@INQTY AS B
RIGHT JOIN
@PO AS A
ON A.IDS = B.CODE
ON A.IDS = C.CODE
WHERE B.CODE IS NOT NULL OR C.CODE IS NOT NULL

[Reeezak]

使用FULL JOIN

[paoluo]

Select
A.*,
B.*,
C.*
From PO A
Left Join InQty B
On A.IDS=B.Code
Left Join OutQty C
On A.IDS=C.Code

[paoluo]

根據PO表，對兩個表左聯聯，不用完全聯接。

[Reeezak]

DECLARE @PO TABLE(IDS INT, ITEMNO VARCHAR(20), ITEMNAME VARCHAR(50), PONO VARCHAR(50))
DECLARE @INQTY TABLE(CODE INT, SUMINQTY INT)
DECLARE @OUTQTY TABLE(CODE INT, SUMOUTQTY INT)


INSERT INTO @PO
SELECT 100, '#3372', 'Splish Food', 'PO-0506-001'
UNION
SELECT 101, '#3373', 'Sky Tools', 'PO-0506-002'

INSERT INTO @INQTY
SELECT 100, 1200

INSERT INTO @OUTQTY
SELECT 101, 2000

SELECT * FROM @PO
SELECT * FROM @INQTY
SELECT * FROM @OUTQTY

SELECT A.*, B.*, C.* FROM
@OUTQTY AS C
RIGHT JOIN
@INQTY AS B
RIGHT JOIN
@PO AS A
ON A.IDS = B.CODE
ON A.IDS = C.CODE

[昵称被占用了]

left join

select a.*,b.SumInQty,c.SumOutQty
from po a left join inqty b on a.ids=b.code
left join outqty c on a.ids=c.code

[itblog]

select isnull(isnull(a.ids,b.code),c.code) as code,itemno,itemname,pono,SumInQty,SumOutQty
from PO a
full join InQty b on a.ids=b.code
full join outQty c on a.ids=c.code

[paoluo]

以PO表為基礎的，所以不需要用Full Join

[itblog]

回楼上，用了full join也会错啊~

[itblog]

回楼上，用了full join也不会错啊~

[lliang0488]

是以PO表為基礎，找到匹配的收發貨記錄，再顯示出來

[Reeezak]

刚开始的时候看成
無發貨記錄表
和
無收貨記錄表了
hoho

[paoluo]

是不會錯，但是沒必要。

[Reeezak]

后面写sql的时候用了right join，以PO表为基础

[lliang0488]

給個正確的完整的連接代碼吧

[paoluo]

不是寫了嗎？？

以上不正確？？還是不完整？？

[paoluo]

這個意思？？

Create View List
As
Select
A.*,
B.SumInQty,
C.SumOutQty
From PO A
Left Join InQty B
On A.IDS=B.Code
Left Join OutQty C
On A.IDS=C.Code
GO

[lliang0488]

謝謝各位，測試以後馬上結帖，再次感謝眾多師兄的指教!