每條訂單明細對應的每個庫存數量都要顯示出來(三個表，交叉連接？)

[SixBank]

有三個表：

表：Warehouse
倉庫代碼 倉庫名稱
s1 1倉庫
s2 2倉庫
s3 3倉庫
s4 4倉庫
s5 5倉庫

表：CurrentStock
倉庫代碼 存貨編碼 現存數量
s1 A 10
s2 A 20
s1 B 100
s2 B 200
s3 B 300
s1 C 1000

表：S_Order
訂單號 客戶編碼 存貨編碼 數量
001 C1 A 30
002 C2 B 40
003 C3 C 50
004 C3 D 60


希望查詢如下結果：每條訂單明細對應的每個庫存數量都要顯示出來，現存量表查沒有的以0顯示
-----------------------------------------------------------------

訂單號 客戶編碼 存貨編碼 數量 倉庫代碼 現存數量
001 C1 A 30 s1 10
001 C1 A 30 s2 20
001 C1 A 30 s3 0
001 C1 A 30 s4 0
001 C1 A 30 s5 0
002 C2 B 40 s1 100
002 C2 B 40 s2 200
002 C2 B 40 s3 300
002 C2 B 40 s4 0
002 C2 B 40 s5 0
003 C3 C 50 s1 1000
003 C3 C 50 s2 0
003 C3 C 50 s3 0
003 C3 C 50 s4 0
003 C3 C 50 s5 0
004 C4 D 60 s1 1000
004 C4 D 60 s2 0
004 C4 D 60 s3 0
004 C4 D 60 s4 0
004 C4 D 60 s5 0

[SixBank]

希望查詢如下結果：每條訂單明細對應的每個庫存數量都要顯示出來，現存量表查沒有的以0顯示

-----------------------------------------------------------------
訂單號 客戶編碼 存貨編碼 數量 倉庫代碼 現存數量
001 C1 A 30 s1 10
001 C1 A 30 s2 20
001 C1 A 30 s3 0
001 C1 A 30 s4 0
001 C1 A 30 s5 0
002 C2 B 40 s1 100
002 C2 B 40 s2 200
002 C2 B 40 s3 300
002 C2 B 40 s4 0
002 C2 B 40 s5 0
003 C3 C 50 s1 1000
003 C3 C 50 s2 0
003 C3 C 50 s3 0
003 C3 C 50 s4 0
003 C3 C 50 s5 0
004 C4 D 60 s1 1000
004 C4 D 60 s2 0
004 C4 D 60 s3 0
004 C4 D 60 s4 0
004 C4 D 60 s5 0

[SixBank]

訂單號	客戶編碼	存貨編碼	數量	倉庫代碼	現存數量
001	C1	A	30	s1	10
001	C1	A	30	s2	20
001	C1	A	30	s3	0
001	C1	A	30	s4	0
001	C1	A	30	s5	0
002	C2	B	40	s1	100
002	C2	B	40	s2	200
002	C2	B	40	s3	300
002	C2	B	40	s4	0
002	C2	B	40	s5	0
003	C3	C	50	s1	1000
003	C3	C	50	s2	0
003	C3	C	50	s3	0
003	C3	C	50	s4	0
003	C3	C	50	s5	0
004	C4	D	60	s1	1000
004	C4	D	60	s2	0
004	C4	D	60	s3	0
004	C4	D	60	s4	0
004	C4	D	60	s5	0

[SixBank]

不好排版，空格被去掉了，請個位教上面查詢如何寫好？

[duanzhi1984]

回答如下：
CREATE TABLE #Warehouse (wareid varchar(10),warename varchar(20))
insert into #Warehouse
select 's1','1倉庫' union all 
select 's2','2倉庫' union all
select 's3','3倉庫' union all
select 's4','4倉庫' union all
select 's5','5倉庫' 
create table #CurrentStock(wareid varchar(10),partid varchar(10),qty int)
insert into #CurrentStock
select 's1','A', 10 union all 
select 's2','A', 20 union all 
select 's1','B', 100 union all 
select 's2','B', 200 union all  
select 's3','B', 300 union all 
select 's1','C', 1000
create table #S_Order(ordID varchar(10),customerID varchar(10),partID varchar(10),qty int)
insert into #S_Order
select '001','C1','A', 30  union all 
select '002' ,'C2','B', 40 union all 
select '003' ,'C3','C', 50 union all 
select '004' ,'C3','D', 60


select a.ordID ,a.customerID ,a.partID ,a.qty, b.wareid,c.qty from #S_Order a join  #Warehouse b on 1=1  
left join #CurrentStock c on c.wareid=b.wareid and c.partid=a.partID

/*
001	C1	A	30	s1	10
001	C1	A	30	s2	20
001	C1	A	30	s3	NULL
001	C1	A	30	s4	NULL
001	C1	A	30	s5	NULL
002	C2	B	40	s1	100
002	C2	B	40	s2	200
002	C2	B	40	s3	300
002	C2	B	40	s4	NULL
002	C2	B	40	s5	NULL
003	C3	C	50	s1	1000
003	C3	C	50	s2	NULL
003	C3	C	50	s3	NULL
003	C3	C	50	s4	NULL
003	C3	C	50	s5	NULL
004	C3	D	60	s1	NULL
004	C3	D	60	s2	NULL
004	C3	D	60	s3	NULL
004	C3	D	60	s4	NULL
004	C3	D	60	s5	NULL */

[duanzhi1984]

去除NULL --isnull(c.qty,0)

select a.ordID ,a.customerID ,a.partID ,a.qty, b.wareid,isnull(c.qty,0) qty from #S_Order a join  #Warehouse b on 1=1  
left join #CurrentStock c on c.wareid=b.wareid and c.partid=a.partID

[永生天地]

if object_id('Warehouse') is not null drop table Warehouse
go
create table Warehouse(倉庫代碼 varchar(2), 倉庫名稱 varchar(8))
insert into Warehouse
select 's1', '1倉庫' union all
select 's2', '2倉庫' union all
select 's3', '3倉庫' union all
select 's4', '4倉庫' union all
select 's5', '5倉庫' ;
go
if object_id('CurrentStock') is not null drop table CurrentStock
go
create table CurrentStock(倉庫代碼 varchar(2), 存貨編碼 varchar(8),現存數量 int)
insert into CurrentStock
select 's1','A', 10 union all
select 's2','A', 20 union all
select 's1','B', 100 union all
select 's2','B', 200 union all
select 's3','B', 300 union all
select 's1','C', 1000;
go
if object_id('S_Order') is not null drop table S_Order
go
create table S_Order(訂單號 varchar(3),客戶編碼 varchar(2), 存貨編碼 varchar(8),數量 int)
insert into S_Order
select '001','C1','A', 30  union all
select '002','C2','B', 40  union all
select '003','C3','C', 50  union all
select '004','C3','D', 60 ;
go
select c.訂單號, c.客戶編碼 ,c.存貨編碼 ,c.數量 ,a.倉庫代碼 ,isnull(b.現存數量,0) 現存數量
from S_Order c cross join Warehouse a left join CurrentStock b on a.倉庫代碼=b.倉庫代碼 and c.存貨編碼=b.存貨編碼

/*
訂單號  客戶編碼 存貨編碼     數量          倉庫代碼 現存數量
---- ---- -------- ----------- ---- -----------
001  C1   A        30          s1   10
001  C1   A        30          s2   20
001  C1   A        30          s3   0
001  C1   A        30          s4   0
001  C1   A        30          s5   0
002  C2   B        40          s1   100
002  C2   B        40          s2   200
002  C2   B        40          s3   300
002  C2   B        40          s4   0
002  C2   B        40          s5   0
003  C3   C        50          s1   1000
003  C3   C        50          s2   0
003  C3   C        50          s3   0
003  C3   C        50          s4   0
003  C3   C        50          s5   0
004  C3   D        60          s1   0
004  C3   D        60          s2   0
004  C3   D        60          s3   0
004  C3   D        60          s4   0
004  C3   D        60          s5   0

(20 行受影响)

*/

[SixBank]

謝謝，結貼