27,579
社区成员
发帖
与我相关
我的任务
分享
create table A(物品 varchar(10), 规格 varchar(10), 库存 int)
insert into A values('ob1', 's11', 10 )
insert into A values('ob1', 's13', 20 )
insert into A values('ob2', 's21', 15 )
insert into A values('ob2', 's24', 13 )
create table B(物品 varchar(10), 型号 varchar(10))
insert into B values('ob1', 'M1')
insert into B values('ob2', 'M2')
create table C(型号 varchar(10), 规格 varchar(10))
insert into C values('M1', 's11')
insert into C values('M1', 's12')
insert into C values('M1', 's13')
insert into C values('M2', 's21')
insert into C values('M2', 's22')
insert into C values('M2', 's23')
insert into C values('M2', 's24')
insert into C values('M3', 's31')
insert into C values('M3', 's32')
go
select t2.物品,t1.规格,isnull(t3.库存,0) 库存 from C t1
inner join B t2 on t1.型号=t2.型号
left join A t3 on t3.物品=t2.物品 and t3.规格=t1.规格
/*
物品 规格 库存
ob1 s11 10
ob1 s12 0
ob1 s13 20
ob2 s21 15
ob2 s22 0
ob2 s23 0
ob2 s24 13
*/
drop table a,b,c
create table A(物品 varchar(10), 规格 varchar(10), 库存 int)
insert into A values('ob1', 's11', 10 )
insert into A values('ob1', 's13', 20 )
insert into A values('ob2', 's21', 15 )
insert into A values('ob2', 's24', 13 )
create table B(物品 varchar(10), 型号 varchar(10))
insert into B values('ob1', 'M1')
insert into B values('ob2', 'M2')
create table C(型号 varchar(10), 规格 varchar(10))
insert into C values('M1', 's11')
insert into C values('M1', 's12')
insert into C values('M1', 's13')
insert into C values('M2', 's21')
insert into C values('M2', 's22')
insert into C values('M2', 's23')
insert into C values('M2', 's24')
insert into C values('M3', 's31')
insert into C values('M3', 's32')
go
select B.物品,C.规格 , isnull(A.库存, 0) 库存 from B
inner join C on B.型号 = c.型号
left join A on B.物品 = A.物品 and C.规格 = A.规格
drop table A,B,C
/*
物品 规格 库存
---------- ---------- -----------
ob1 s11 10
ob1 s12 0
ob1 s13 20
ob2 s21 15
ob2 s22 0
ob2 s23 0
ob2 s24 13
(7 行受影响)
*/
create table A(物品 varchar(10), 规格 varchar(10), 库存 int)
insert into A values('ob1', 's11', 10 )
insert into A values('ob1', 's13', 20 )
insert into A values('ob2', 's21', 15 )
insert into A values('ob2', 's24', 13 )
create table B(物品 varchar(10), 型号 varchar(10))
insert into B values('ob1', 'M1')
insert into B values('ob2', 'M2')
create table C(型号 varchar(10), 规格 varchar(10))
insert into C values('M1', 's11')
insert into C values('M1', 's12')
insert into C values('M1', 's13')
insert into C values('M2', 's21')
insert into C values('M2', 's22')
insert into C values('M2', 's23')
insert into C values('M2', 's24')
insert into C values('M3', 's31')
insert into C values('M3', 's32')
go
select m.* , isnull(A.库存, 0) 库存 from
(select B.物品,C.规格 from B,C where B.型号 = c.型号) m
left join A on m.物品 = A.物品 and m.规格 = A.规格
drop table A,B,C
/*
物品 规格 库存
---------- ---------- -----------
ob1 s11 10
ob1 s12 0
ob1 s13 20
ob2 s21 15
ob2 s22 0
ob2 s23 0
ob2 s24 13
(7 行受影响)
*/
select m.* , left(A.库存, 0) 库存 from
(select B.物品,C.规格 from B,C where B.型号 = c.型号) m
left join A on m.物品 = A.物品 and m.规格 = A.规格
select b.物品,c.规格,isnull(a.库存,0) as 库存
from b inner join c on b.型号=c.型号
left join a on a.物品=b.物品 and a.规格=c.规格
order by b.物品,c.规格
select b.物品,c.规格,isnull(a.库存,0) as 库存
from b inner join c on b.型号=c.型号
left join a on a.物品=b.物品 and a.规格=c.规格