求一段SQL代码,谢谢大家!
表:product(产品表)
pro_id pro_name pro_lower(库存下限) pro_upper(库存上限)
1 电视 5 50
2 水果 10 60
表:stock(库存表)
sto_id pro_id pro_amount(数量) pro_stoarage(所在仓库)
1 1 3 1
2 1 1 2
3 2 30 1
4 2 10 2
表:storage (仓库表)
storage_id storage_name
1 主仓库
2 家电库
如何在存储过程中汇总得到:
pro_id pro_name pro_amount(总数量) 主仓库(数量) 家电库(数量) ...
1 电视 4 3 1
2 水果 40 30 10
并且能分别查询出:
库存总数量 <产品表中产品下限的产品
库存总数量> 产品表中产品上限的产品
[解决办法]
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',[ '+storage_name
+ '(数量)]=sum(case pro_stoarage when '+rtrim(storage_id)
+ ' then pro_amount else 0 end) '
from storage
set @sql= 'select p.pro_id,p.pro_name,pro_amount=sum(pro_amount) '+@sql
+ ' from product p,stock s where p.pro_id=s.pro_id group by p.pro_id,p.pro_name '
exec(@sql)
[解决办法]
create table product(pro_id int,pro_name varchar(1000),pro_lower int,pro_upper int)
insert into product select 1, '电视 ',5,50
union select 2, '水果 ',10,60
create table stock(sto_id int,pro_id int,pro_amount int,pro_stoarage int)
insert into stock select 1,1,3,1
union select 2,1,1,2
union select 3,2,30,1
union select 4,2,10,2
create table storage(storage_id int,storage_name varchar(100))
insert into storage select 1, '主仓库 '
union select 2, '家电库 '
/*非动态*/
select
a.pro_id,
b.pro_name,
pro_amount=sum(a.pro_amount),
'主仓库数量 '=sum(case when a.pro_stoarage=1 then a.pro_amount else 0 end),
'家电库数量 '=sum(case when a.pro_stoarage=2 then a.pro_amount else 0 end)
from
stock a left join product b on a.pro_id=b.pro_id left join
storage c on a.pro_stoarage=c.storage_id group by a.pro_id,b.pro_name
/*动态*/
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ 'select a.pro_id,b.pro_name,pro_amount=sum(a.pro_amount), '
select @sql=@sql+ ' ' ' '+ltrim(storage_name)+ '数量 ' '=sum(case when a.pro_stoarage= '+ltrim(storage_id)+ ' then a.pro_amount else 0 end), '
from storage
select @sql=left(@sql,len(@sql)-1)
select @sql=@sql+ ' from stock a left join product b on a.pro_id=b.pro_id left join
storage c on a.pro_stoarage=c.storage_id group by a.pro_id,b.pro_name '
print @sql
在storage (仓库表) 行值不定时
