22,199
社区成员
发帖
与我相关
我的任务
分享
select
a.Name as 药品名称,
b.shopName as 药店名称,
sum(Xiaoshoushuliang)销售数量,
sum(shijishuliang)进货数量,
sum(Kusunshuliang)库存数量,
a.Pizhunwenhao as 批号
from ws_IOA_AfterGoods a
inner join ws_info_shop b on a.shopId=b.id
left join ws_IOA_OutGoods c on a.Name=c.name and a.Pizhunwenhao=c.Pizhunwenhao
left join ws_IOA_InGoods d on a.name=d.name and a.Pizhunwenhao=d.Pizhunwenhao
group by a.name,b.shopName,a.Pizhunwenhao
/*
药品名称 药店名称 销售数量 进货数量 库存数量 批号
---------- ---------- ----------- ----------- ----------- --------------------
大力丸 不要钱药店 630 1890 1470 国药准字h36022108
大力丸 抢你钱药店 420 1260 1470 国药准字h36022108
(2 行受影响)
*/
select t.name,c.shopName,t.Pizhunwenhao,
max(t.[销售数量]) [销售数量],max(t.[进货数量]) [进货数量],max(t.[库存数量]) [库存数量]
from(
select name,sum(Xiaoshoushuliang) [销售数量],0 as [进货数量],0 as [库存数量],Pizhunwenhao,shopid
from ws_IOA_OutGoods group by name,Pizhunwenhao,shopid
union all
select name,0 as [销售数量],sum(shijishuliang) [进货数量],0 as [库存数量],Pizhunwenhao,shopid
from ws_IOA_InGoods group by name,Pizhunwenhao,shopid
union all
select name,0 as [销售数量],0 as [进货数量],sum(Kusunshuliang) [库存数量],Pizhunwenhao,shopid
from ws_IOA_AfterGoods group by name,Pizhunwenhao,shopid
)t join ws_info_shop c on t.shopid = c.id
--where 这里可以加条件,比如 c.shopName = '不要钱药店'
group by t.name,t.Pizhunwenhao,c.shopName
/*销售表*/
declare @ws_IOA_OutGoods table
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10))
insert into @ws_IOA_OutGoods
select 1,5,'国药准字h36022108','大力丸' union all
select 1,3,'国药准字h36022108','大力丸' union all
select 1,2,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,5,'国药准字h36022108','大力丸' union all
select 2,3,'国药准字h36022108','大力丸' union all
select 2,2,'国药准字h36022108','大力丸'
/*进货表*/
declare @ws_IOA_InGoods table
(shopId int,shijishuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into @ws_IOA_InGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,13,'国药准字h36022108','大力丸' union all
select 1,12,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,15,'国药准字h36022108','大力丸' union all
select 2,13,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*库存表 ,,,也有可能有多行记录*/
declare @ws_IOA_AfterGoods table
(shopId int,Kusunshuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into @ws_IOA_AfterGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 1,5,'国药准字h36022108','大力丸' union all
select 2,18,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*药店信息表*/
declare @ws_info_shop table
(Id int,shopName varchar(10))
insert into @ws_info_shop
select 1,'不要钱药店' union all
select 2,'抢你钱药店'
select
a.Name as 药品名称 ,d.shopName as 药店名称 ,a.Xiaoshoushuliang as 销售数量 ,
b.shijishuliang as 进货数量 ,c.Kusunshuliang as 库存数量 ,c.Pizhunwenhao as 批号
from (select shopId,Name,sum(Xiaoshoushuliang) as Xiaoshoushuliang from @ws_IOA_OutGoods
group by shopId,Name) a left join
(select shopId,Name,sum(shijishuliang) as shijishuliang from @ws_IOA_InGoods group by shopId,Name) b
on a.Name=b.Name and a.shopId=b.shopId left join
(select Name,sum(Kusunshuliang) as Kusunshuliang,shopId,
min(Pizhunwenhao) as Pizhunwenhao from @ws_IOA_AfterGoods
group by shopId,Name) c on a.Name=c.Name and a.shopId=c.shopId
left join @ws_info_shop d on c.shopId=d.Id
where a.Name='大力丸' and c.Pizhunwenhao='国药准字h36022108' --条件
/*
药品名称 药店名称 销售数量 进货数量 库存数量 批号
---------- ---------- ----------- ----------- ----------- --------------------
大力丸 不要钱药店 20 50 30 国药准字h36022108
大力丸 抢你钱药店 10 40 30 国药准字h36022108
*/
select t.name,c.shopName,t.Pizhunwenhao,
max(t.[销售数量]) [销售数量],max(t.[进货数量]) [进货数量],max(t.[库存数量]) [库存数量]
from(
select name,sum(Xiaoshoushuliang) [销售数量],0 as [进货数量],0 as [库存数量],Pizhunwenhao,shopid
from ws_IOA_OutGoods group by name,Pizhunwenhao,shopid
union all
select name,0 as [销售数量],sum(shijishuliang) [进货数量],0 as [库存数量],Pizhunwenhao,shopid
from ws_IOA_InGoods group by name,Pizhunwenhao,shopid
union all
select name,0 as [销售数量],0 as [进货数量],sum(Kusunshuliang) [库存数量],Pizhunwenhao,shopid
from ws_IOA_AfterGoods group by name,Pizhunwenhao,shopid
)t join ws_info_shop c on t.shopid = c.id
--where
group by t.name,t.Pizhunwenhao,c.shopName
drop table ws_IOA_OutGoods,ws_IOA_InGoods,ws_IOA_AfterGoods,ws_info_shop
/*销售表*/
create table ws_IOA_OutGoods
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10))
insert into ws_IOA_OutGoods
select 1,5,'国药准字h36022108','大力丸' union all
select 1,3,'国药准字h36022108','大力丸' union all
select 1,2,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,5,'国药准字h36022108','大力丸' union all
select 2,3,'国药准字h36022108','大力丸' union all
select 2,2,'国药准字h36022108','大力丸'
/*进货表*/
create table ws_IOA_InGoods
(shopId int,shijishuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into ws_IOA_InGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,13,'国药准字h36022108','大力丸' union all
select 1,12,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,15,'国药准字h36022108','大力丸' union all
select 2,13,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*库存表 ,,,也有可能有多行记录*/
create table ws_IOA_AfterGoods
(shopId int,Kusunshuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into ws_IOA_AfterGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 1,5,'国药准字h36022108','大力丸' union all
select 2,18,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*药店信息表*/
create table ws_info_shop
(Id int,shopName varchar(10))
insert into ws_info_shop
select 1,'不要钱药店' union all
select 2,'抢你钱药店'
go
select t.name,c.shopName,t.Pizhunwenhao,
max(t.[销售数量]) [销售数量],max(t.[进货数量]) [进货数量],max(t.[库存数量]) [库存数量]
from(
select name,sum(Xiaoshoushuliang) [销售数量],0 as [进货数量],0 as [库存数量],Pizhunwenhao,shopid
from ws_IOA_OutGoods group by name,Pizhunwenhao,shopid
union all
select name,0 as [销售数量],sum(shijishuliang) [进货数量],0 as [库存数量],Pizhunwenhao,shopid
from ws_IOA_InGoods group by name,Pizhunwenhao,shopid
union all
select name,0 as [销售数量],0 as [进货数量],sum(Kusunshuliang) [库存数量],Pizhunwenhao,shopid
from ws_IOA_AfterGoods group by name,Pizhunwenhao,shopid
)t join ws_info_shop c on t.shopid = c.id
--where
group by t.name,t.Pizhunwenhao,c.shopName
drop table ws_IOA_OutGoods,ws_IOA_InGoods,ws_IOA_AfterGoods,ws_info_shop
/**********
name shopName Pizhunwenhao 销售数量 进货数量 库存数量
---------- ---------- -------------------- ----------- ----------- -----------
大力丸 不要钱药店 国药准字h36022108 20 50 30
大力丸 抢你钱药店 国药准字h36022108 10 40 30
(2 行受影响)
/*销售表*/
declare @ws_IOA_OutGoods table
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10))
insert into @ws_IOA_OutGoods
select 1,5,'国药准字h36022108','大力丸' union all
select 1,3,'国药准字h36022108','大力丸' union all
select 1,2,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,5,'国药准字h36022108','大力丸' union all
select 2,3,'国药准字h36022108','大力丸' union all
select 2,2,'国药准字h36022108','大力丸'
/*进货表*/
declare @ws_IOA_InGoods table
(shopId int,shijishuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into @ws_IOA_InGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,13,'国药准字h36022108','大力丸' union all
select 1,12,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,15,'国药准字h36022108','大力丸' union all
select 2,13,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*库存表 ,,,也有可能有多行记录*/
declare @ws_IOA_AfterGoods table
(shopId int,Kusunshuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into @ws_IOA_AfterGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 1,5,'国药准字h36022108','大力丸' union all
select 2,18,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*药店信息表*/
declare @ws_info_shop table
(Id int,shopName varchar(10))
insert into @ws_info_shop
select 1,'不要钱药店' union all
select 2,'抢你钱药店'
select
a.Name as 药品名称 ,d.shopName as 药店名称 ,a.Xiaoshoushuliang as 销售数量 ,
b.shijishuliang as 进货数量 ,c.Kusunshuliang as 库存数量 ,c.Pizhunwenhao as 批号
from (select shopId,Name,sum(Xiaoshoushuliang) as Xiaoshoushuliang from @ws_IOA_OutGoods
group by shopId,Name) a left join
(select shopId,Name,sum(shijishuliang) as shijishuliang from @ws_IOA_InGoods group by shopId,Name) b
on a.Name=b.Name and a.shopId=b.shopId left join
(select Name,sum(Kusunshuliang) as Kusunshuliang,shopId,
min(Pizhunwenhao) as Pizhunwenhao from @ws_IOA_AfterGoods
group by shopId,Name) c on a.Name=c.Name and a.shopId=c.shopId
left join @ws_info_shop d on c.shopId=d.Id
/*
药品名称 药店名称 销售数量 进货数量 库存数量 批号
---------- ---------- ----------- ----------- ----------- --------------------
大力丸 不要钱药店 20 50 30 国药准字h36022108
大力丸 抢你钱药店 10 40 30 国药准字h36022108
*/
/*销售表*/
declare @ws_IOA_OutGoods table
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10))
insert into @ws_IOA_OutGoods
select 1,5,'国药准字h36022108','大力丸' union all
select 1,3,'国药准字h36022108','大力丸' union all
select 1,2,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,5,'国药准字h36022108','大力丸' union all
select 2,3,'国药准字h36022108','大力丸' union all
select 2,2,'国药准字h36022108','大力丸'
/*进货表*/
declare @ws_IOA_InGoods table
(shopId int,shijishuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into @ws_IOA_InGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,13,'国药准字h36022108','大力丸' union all
select 1,12,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,15,'国药准字h36022108','大力丸' union all
select 2,13,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*库存表 ,,,也有可能有多行记录*/
declare @ws_IOA_AfterGoods table
(shopId int,Kusunshuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into @ws_IOA_AfterGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 1,5,'国药准字h36022108','大力丸' union all
select 2,18,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*药店信息表*/
declare @ws_info_shop table
(Id int,shopName varchar(10))
insert into @ws_info_shop
select 1,'不要钱药店' union all
select 2,'抢你钱药店'
select
a.Name as 药品名称 ,d.shopName as 药店名称 ,a.Xiaoshoushuliang as 销售数量 ,
b.shijishuliang as 进货数量 ,c.Kusunshuliang as 库存数量 ,c.Pizhunwenhao as 批号
from (select Name,sum(Xiaoshoushuliang) as Xiaoshoushuliang from @ws_IOA_OutGoods
group by Name) a left join
(select Name,sum(shijishuliang) as shijishuliang from @ws_IOA_InGoods group by Name) b
on a.Name=b.Name left join
(select Name,sum(Kusunshuliang) as Kusunshuliang,min(shopId) as shopId,
min(Pizhunwenhao) as Pizhunwenhao from @ws_IOA_AfterGoods
group by Name) c on a.Name=c.Name
left join @ws_info_shop d on c.shopId=d.Id
/*
药品名称 药店名称 销售数量 进货数量 库存数量 批号
---------- ---------- ----------- ----------- ----------- --------------------
大力丸 不要钱药店 30 90 60 国药准字h36022108
*/
/*销售表*/
create table ws_IOA_OutGoods
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10))
insert into ws_IOA_OutGoods
select 1,5,'国药准字h36022108','大力丸' union all
select 1,3,'国药准字h36022108','大力丸' union all
select 1,2,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,5,'国药准字h36022108','大力丸' union all
select 2,3,'国药准字h36022108','大力丸' union all
select 2,2,'国药准字h36022108','大力丸'
/*进货表*/
create table ws_IOA_InGoods
(shopId int,shijishuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into ws_IOA_InGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,13,'国药准字h36022108','大力丸' union all
select 1,12,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 2,15,'国药准字h36022108','大力丸' union all
select 2,13,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*库存表 ,,,也有可能有多行记录*/
create table ws_IOA_AfterGoods
(shopId int,Kusunshuliang int,Pizhunwenhao varchar(20),Name varchar(10))
insert into ws_IOA_AfterGoods
select 1,15,'国药准字h36022108','大力丸' union all
select 1,10,'国药准字h36022108','大力丸' union all
select 1,5,'国药准字h36022108','大力丸' union all
select 2,18,'国药准字h36022108','大力丸' union all
select 2,12,'国药准字h36022108','大力丸'
/*药店信息表*/
create table ws_info_shop
(Id int,shopName varchar(10))
insert into ws_info_shop
select 1,'不要钱药店' union all
select 2,'抢你钱药店'
go
declare @sql varchar(8000)
set @sql = 'select b.shopname,a.name,a.shopid,a.Pizhunwenhao'
select name,xsum,Pizhunwenhao,shopid,fenlei
into #tb
from(
select name,sum(Xiaoshoushuliang) xsum,Pizhunwenhao,shopid,'销售数量' fenlei
from ws_IOA_OutGoods group by name,Pizhunwenhao,shopid
union all
select name,sum(shijishuliang) xsum,Pizhunwenhao,shopid,'进货数量' fenlei
from ws_IOA_InGoods group by name,Pizhunwenhao,shopid
union all
select name,sum(Kusunshuliang) xsum,Pizhunwenhao,shopid,'库存数量' fenlei
from ws_IOA_AfterGoods group by name,Pizhunwenhao,shopid
)t
select @sql = @sql + ',sum(case a.fenlei when ''' + fenlei + ''' then a.xsum else 0 end) ['+fenlei+']'
from(select distinct fenlei from #tb)t
select @sql = @sql + ' from #tb a,ws_info_shop b where a.shopid = b.id group by b.shopname,a.name,a.shopid,a.Pizhunwenhao'
exec(@sql)
drop table ws_IOA_OutGoods,ws_IOA_InGoods,ws_IOA_AfterGoods,ws_info_shop,#tb
/************
shopname name shopid Pizhunwenhao 进货数量 库存数量 销售数量
---------- ---------- ----------- -------------------- ----------- ----------- -----------
不要钱药店 大力丸 1 国药准字h36022108 50 30 20
抢你钱药店 大力丸 2 国药准字h36022108 40 30 10
(2 行受影响)
declare @销售表 table
(药品名称 varchar(10),销售数量 int,批号 varchar(20),
药店ID varchar(4),药店名称 varchar(10))
insert into @销售表
select '大力丸',3,'国药准字h36022108','0011','不要钱药店' union all
select '大力丸',6,'国药准字h36022108','0011','不要钱药店' union all
select '大力丸',11,'国药准字h36022108','0011','不要钱药店' union all
select '大力丸2',11,'国药准字h36022109','0011','不要钱药店' union all
select '大力丸2',22,'国药准字h36022109','0011','不要钱药店'
declare @进货表 table
(进货名称 varchar(10),进货数量 int,批号 varchar(20),药店ID varchar(4))
insert into @进货表
select '大力丸',20,'国药准字h36022108','0011' union all
select '大力丸',40,'国药准字h36022108','0011' union all
select '大力丸',40,'国药准字h36022108','0011' union all
select '大力丸2',11,'国药准字h36022109','0011' union all
select '大力丸2',11,'国药准字h36022109','0011'
declare @库存表 table
(库存名称 varchar(10),库存数量 int,批号 varchar(17),药店ID varchar(4),药店名称 varchar(10))
insert into @库存表
select '大力丸',80,'国药准字h36022108','0011','不要钱药店' union all
select '大力丸2',1,'国药准字h36022109','0011','不要钱药店'
select a.药品名称,c.药店名称,a.销售数量 ,b.进货数量,c.库存数量 ,c.批号 from (
select 药品名称,sum(销售数量) as 销售数量 from @销售表 group by 药品名称
) a left join
(select 进货名称,sum(进货数量) as 进货数量 from @进货表 group by 进货名称) b
on a.药品名称=b.进货名称
left join @库存表 c on a.药品名称=c.库存名称
/*
药品名称 药店名称 销售数量 进货数量 库存数量 批号
---------- ---------- ----------- ----------- ----------- -----------------
大力丸 不要钱药店 20 100 80 国药准字h36022108
大力丸2 不要钱药店 33 22 1 国药准字h36022109
*/
--新添加了一个大力丸2
declare @销售表 table
(药品名称 varchar(6),销售数量 int,批号 varchar(20),
药店ID varchar(4),药店名称 varchar(10))
insert into @销售表
select '大力丸',3,'国药准字h36022108','0011','不要钱药店' union all
select '大力丸',6,'国药准字h36022108','0011','不要钱药店' union all
select '大力丸',11,'国药准字h36022108','0011','不要钱药店'
declare @进货表 table
(进货名称 varchar(6),进货数量 int,批号 varchar(20),药店ID varchar(4))
insert into @进货表
select '大力丸',20,'国药准字h36022108','0011' union all
select '大力丸',40,'国药准字h36022108','0011' union all
select '大力丸',40,'国药准字h36022108','0011'
declare @库存表 table
(库存名称 varchar(6),库存数量 int,批号 varchar(17),药店ID varchar(4),药店名称 varchar(10))
insert into @库存表
select '大力丸',80,'国药准字h36022108','0011','不要钱药店'
select a.药品名称,c.药店名称,a.销售数量 ,b.进货数量,c.库存数量 ,c.批号 from (
select 药品名称,sum(销售数量) as 销售数量 from @销售表 group by 药品名称
) a left join
(select 进货名称,sum(进货数量) as 进货数量 from @进货表 group by 进货名称) b
on a.药品名称=b.进货名称
left join @库存表 c on a.药品名称=c.库存名称
/*
药品名称 药店名称 销售数量 进货数量 库存数量 批号
------ ---------- ----------- ----------- ----------- -----------------
大力丸 不要钱药店 20 100 80 国药准字h36022108
*/