GOODS表
ID NAME
1 a
2 B
3 C
STORE 表
ID NAME
1 A仓
2 B仓
3 C仓
4 D仓
GOODSSTORE表
ID GOODSID STOREID 数量
1 2 1 10
2 2 2 15
3 3 1 10
要求得到
GOODSID为参数=2
STORE 数量
A仓 10
B仓 15
C仓 0
D仓 0
ID NAME
1 a
2 B
3 C
STORE 表
ID NAME
1 A仓
2 B仓
3 C仓
4 D仓
GOODSSTORE表
ID GOODSID STOREID 数量
1 2 1 10
2 2 2 15
3 3 1 10
要求得到
GOODSID为参数=2
STORE 数量
A仓 10
B仓 15
C仓 0
D仓 0
from STORE a left join (
select STOREID,sum(数量) as 数量 from GOODSSTORE
where GOODSID=2
group by STOREID
) as t
on a.id=t.STOREID
from STORE a left join (
select STOREID,sum(数量) as 数量 from GOODSSTORE
where GOODSID=2
group by STOREID
) as t
on a.id=t.STOREID
declare @goods table(id int,name varchar(10))
insert @goods
select 1,'a' union all
select 2,'B' union all
select 3,'C'
declare @store table(id int,name varchar(10))
insert @store
select 1,'A仓' union all
select 2,'B仓' union all
select 3,'C仓' union all
select 4,'D仓'
declare @goodsstore table(id int,goodsid int,storeid int,数量 int )
insert @goodsstore
select 1,2,1,10 union all
select 2,2,2,15 union all
select 3,3,1,10
----查询
select a.name as store,isnull(sum(数量),0) as 数量
from @store a left join @goodsstore b on a.id = b.storeid and b.goodsid = 2
group by a.name
(
ID int identity(1,1),
NAME varchar(2)
)
insert into goods
select 'A' union all
select 'B' union all
select 'C'select * from GOODSCreate table STORE
(
ID int identity(1,1),
name varchar(10)
)
insert into Store
select 'A仓' union all
select 'B仓' union all
select 'C仓' union all
select 'D仓'
SELECT * FROM STOREcreate table goodsstore
(
id int identity(1,1),
goodsid int,
storeid int,
数量 int
)
insert into goodsstore
select 2,1,10 union all
select 2,2,15 union all
select 3,1,10
select * from goodsstoreCreate Proc protest
@goodsid int
as
select a.NAME as STORE,isnull(t.数量,0) as 数量
from STORE a left join (
select STOREID,sum(数量) as 数量 from GOODSSTORE
where GOODSID=@goodsid
group by STOREID
) as t
on a.id=t.STOREID--调用
exec protest 2