在sql2005中
有3张表: OutGoods(销售表): 药品名称(Name) 销售数量(xiaoshoushuliang) 批号(pihao) 药店ID(shopId) 等字段 (同一件商品有多条销售记录)
InGoods(进货表): 进货名称(Name) 进货数量(jinhuoshuliang) 批号(pihao) 药店ID(shopId) 等字段 (同一件商品有多条进货记录)
AfterGoods(库存表):库存名称(Name) 库存数量(kucunshuliang) 批号(pihao) 药店ID(shopId) 等字段
现在我要叫查询的结果在datagridview 中显示 。显示格式如下:
药品名称 药店名称 销售数量 进货数量 库存数量 批号
例: 大力丸 不要钱药店 20 100 80 国药准字h36022108
通过 药品名称 或 批号进行查询。。
求高手给我写个可行sql语句谢谢了 在线等。
有3张表: OutGoods(销售表): 药品名称(Name) 销售数量(xiaoshoushuliang) 批号(pihao) 药店ID(shopId) 等字段 (同一件商品有多条销售记录)
InGoods(进货表): 进货名称(Name) 进货数量(jinhuoshuliang) 批号(pihao) 药店ID(shopId) 等字段 (同一件商品有多条进货记录)
AfterGoods(库存表):库存名称(Name) 库存数量(kucunshuliang) 批号(pihao) 药店ID(shopId) 等字段
现在我要叫查询的结果在datagridview 中显示 。显示格式如下:
药品名称 药店名称 销售数量 进货数量 库存数量 批号
例: 大力丸 不要钱药店 20 100 80 国药准字h36022108
通过 药品名称 或 批号进行查询。。
求高手给我写个可行sql语句谢谢了 在线等。
declare @sql varchar(8000)
set @sql = 'select name,shopid,pihao'select name,xsum,pihao,shopid,fenlei
into #tb
from(
select name,sum(xiaoshoushuliang) xsum,pihao,shopid,'销售数量' fenlei from outgoods group by name,pihao,shopid
union all
select name,sum(jinhuoshuliang) xsum,pihao,shopid,'进货数量' fenlei from InGoods group by name,pihao,shopid
union all
select name,sum(kucunshuliang) xsum,pihao,shopid,'库存数量' fenlei from AfterGoods group by name,pihao,shopid
)tselect @sql = @sql + ',sum(case fenlei when ''' + fenlei + ''' then xsum else 0 end) ['+fenlei+']'
from(select distinct fenlei from #tb)tselect @sql = @sql + ' from #tb group by name,shopid,pihao'
exec(@sql)
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','不要钱药店'select * from @销售表
/*
药品名称 销售数量 批号 药店ID 药店名称
------ ----------- -------------------- ---- ----------
大力丸 3 国药准字h36022108 0011 不要钱药店
大力丸 6 国药准字h36022108 0011 不要钱药店
大力丸 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'select * from @进货表
/*
进货名称 进货数量 批号 药店ID
------ ----------- -------------------- ----
大力丸 20 国药准字h36022108 0011
大力丸 40 国药准字h36022108 0011
大力丸 40 国药准字h36022108 0011
*/
declare @库存表 table
(库存名称 varchar(6),库存数量 int,批号 varchar(17),药店ID varchar(4))
insert into @库存表
select '大力丸',80,'国药准字h36022108','0011'select * from @库存表
/*
库存名称 库存数量 批号 药店ID
------ ----------- ----------------- ----
大力丸 80 国药准字h36022108 0011
*/
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
*/
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 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,'抢你钱药店' union all/*显示结果 通过药店ID + 药品名称 或 批号查询*/
/********药品名称 药店名称 销售数量 进货数量 库存数量 批号
---------- ---------- ----------- ----------- ----------- -----------------
大力丸 不要钱药店 20 50 30 国药准字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,'抢你钱药店'
godeclare @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
)tselect @sql = @sql + ',sum(case a.fenlei when ''' + fenlei + ''' then a.xsum else 0 end) ['+fenlei+']'
from(select distinct fenlei from #tb)tselect @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 @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
*/
/*销售表*/
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
*/
药店区分的话,就这样!
程序里就用这个:
/*销售表*/
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,'抢你钱药店'
goselect 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.shopNamedrop 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 行受影响)
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.shopNamedrop table ws_IOA_OutGoods,ws_IOA_InGoods,ws_IOA_AfterGoods,ws_info_shop这个么 你没写where条件 说实话 我没看懂你写的这个。。但你写的这个确实没问题
/*销售表*/
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
*/
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
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 行受影响)
*/