来高手。依旧sql语句。。

2011-07-14 10:11:23 334 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Cadence学习之路（六）元器件封装绘制使用Allegro PCB Designer绘制元器件封装阅读详情

在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语句。。。谢谢了。。。在线等。

...全文

深入解析LinuxCNC源码：从界面开发到硬件交互本文深入解析LinuxCNC开源数控系统的源码架构与二次开发实践。详细介绍了从GTK+界面定制、Python脚本绑定，到硬件抽象层(HAL)配置与核心运动控制模块的完整开发流程。通过实战案例，帮助开发者掌握如何基于LinuxCNC源码进行界面开发和硬件交互定制，实现个性化数控系统。阅读详情

q65817758

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

一片冰心在玉壶 2011-07-14

打赏
举报

围观叶子。。。

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）

来高手。依旧sql语句。。

相关推荐