来高手。依旧sql语句。。

q65817758 2011-07-14 10:11:23
在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语句。。。谢谢了 。。。在线等。
...全文
248 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
围观叶子。。。
q65817758 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 22 楼 cd731107 的回复:]
SQL code

select
a.Name as 药品名称,
b.shopName as 药店名称,
sum(Xiaoshoushuliang)销售数量,
sum(shijishuliang)进货数量,
sum(Kusunshuliang)库存数量,
a.Pizhunwenhao as 批号
from ws_IOA_AfterGoods a
inner join w……
[/Quote]


谢谢你了 不过 库存有那么多么
cd731107 2011-07-14
  • 打赏
  • 举报
回复
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 行受影响)
*/
q65817758 2011-07-14
  • 打赏
  • 举报
回复
真的很感谢你们2位。。谢谢。。谢谢。。终于解决了。。
AcHerat 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 18 楼 q65817758 的回复:]

引用 16 楼 acherat 的回复:
引用 14 楼 q65817758 的回复:

引用 12 楼 acherat 的回复:
SQL code


/*销售表*/
create table ws_IOA_OutGoods
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10)……
[/Quote]


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
叶子 2011-07-14
  • 打赏
  • 举报
回复

/*销售表*/
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
*/
q65817758 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 16 楼 acherat 的回复:]
引用 14 楼 q65817758 的回复:

引用 12 楼 acherat 的回复:
SQL code


/*销售表*/
create table ws_IOA_OutGoods
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10))
insert into ws_IOA_O……
[/Quote]


你是说直接用

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



这个么 你没写where条件 说实话 我没看懂你写的这个。。但你写的这个确实没问题
q65817758 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 13 楼 maco_wang 的回复:]
SQL code


/*销售表*/
declare @ws_IOA_OutGoods table
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10))
insert into @ws_IOA_OutGoods
select 1,5,'国药准字h36022108','大力丸' uni……
[/Quote]

你写的这个条件怎么赋给的
AcHerat 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 14 楼 q65817758 的回复:]

引用 12 楼 acherat 的回复:
SQL code


/*销售表*/
create table ws_IOA_OutGoods
(shopId int,Xiaoshoushuliang int,Pizhunwenhao varchar(20),
Name varchar(10))
insert into ws_IOA_OutGoods
select 1,5,'国药准字……
[/Quote]

程序里就用这个:


/*销售表*/
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 行受影响)
叶子 2011-07-14
  • 打赏
  • 举报
回复

/*销售表*/
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
*/


药店区分的话,就这样!
q65817758 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 acherat 的回复:]
SQL code


/*销售表*/
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 ……
[/Quote]


你好,我是新手。。有点没看懂你写的。 你写的这我在程序中怎么用啊 。。麻烦给说说 谢谢
叶子 2011-07-14
  • 打赏
  • 举报
回复

/*销售表*/
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
*/
AcHerat 2011-07-14
  • 打赏
  • 举报
回复

/*销售表*/
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 行受影响)
q65817758 2011-07-14
  • 打赏
  • 举报
回复
/*销售表*/
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

*******/
q65817758 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 acherat 的回复:]
引用 6 楼 q65817758 的回复:

引用 2 楼 acherat 的回复:
SQL code


declare @sql varchar(8000)
set @sql = 'select name,shopid,pihao'

select name,xsum,pihao,shopid,fenlei
into #tb
from(
select name,sum(……
[/Quote]


等等我上表结构 和测试数据 谢谢了。
q65817758 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 maco_wang 的回复:]
SQL code


declare @销售表 table
(药品名称 varchar(10),销售数量 int,批号 varchar(20),
药店ID varchar(4),药店名称 varchar(10))
insert into @销售表
select '大力丸',3,'国药准字h36022108','0011','不要钱药店' union all
select '大力丸……
[/Quote]

表结构不对 等下 我上表结构和测试数据 。。谢谢各位
叶子 2011-07-14
  • 打赏
  • 举报
回复

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
AcHerat 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 q65817758 的回复:]

引用 2 楼 acherat 的回复:
SQL code


declare @sql varchar(8000)
set @sql = 'select name,shopid,pihao'

select name,xsum,pihao,shopid,fenlei
into #tb
from(
select name,sum(xiaoshoushuliang) xsum,p……
[/Quote]

你不给表测试数据怎么行呢? 报什么错!
q65817758 2011-07-14
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 acherat 的回复:]
SQL code


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,'销售数……
[/Quote]


不行 。。
叶子 2011-07-14
  • 打赏
  • 举报
回复

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
*/

加载更多回复(6)

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧