客户拿刀在背后等我。。。高分求教高手，在线等待，下午要：（

Question

declare @s varchar(8000)
set @s=''
select @s=@s+',['+区域+'销售]=sum(case 区域 when '''+区域+''' then FsaleQty（ else 0 end)'
+',['+区域+'库存]=sum(case 区域 when '''+区域+''' then FinvQty else 0 end)'
from 表 group by 区域
exec('select Fnumber,单价=avg(Fsaleprice)'+@s+'
,办事处销售=sum(FsaleQty),办事处库存=sum(FinvQty)
from 表 group by Fnumber')

Answer 1

--写错字段declare @s varchar(8000)
set @s=''
select @s=@s+',['+F_102+'销售]=sum(case F_102 when '''+F_102+''' then FsaleQty（ else 0 end)'
+',['+F_102+'库存]=sum(case 区域 when '''+F_102+''' then FinvQty else 0 end)'
from 表 group by F_102
exec('select Fnumber,单价=avg(Fsaleprice)'+@s+'
,办事处销售=sum(FsaleQty),办事处库存=sum(FinvQty)
from 表 group by Fnumber')

Answer 2

--上面还有点错,再改一下:declare @s varchar(8000)
set @s=''
select @s=@s+',['+F_102+'销售]=sum(case F_102 when '''+F_102+''' then FsaleQty else 0 end)'
+',['+F_102+'库存]=sum(case 区域 when '''+F_102+''' then FinvQty else 0 end)'
from 表 group by F_102
exec('select Fnumber,单价=avg(Fsaleprice)'+@s+'
,办事处销售=sum(FsaleQty),办事处库存=sum(FinvQty)
from 表 group by Fnumber')

Answer 3

好！
这些数据是从:
select * from #ryy
临时表里取出来的
就是这种货物的：
货号，单价,及在F_102(某一区域)的销售和库存量
办事处销售的意思是：各个区域的销售及库存的总合。

Answer 4

--改了declare @s varchar(8000)
set @s=''
select @s=@s+'
,['+F_102+'销售]=sum(case F_102 when '''+F_102+''' then FsaleQty else 0 end)'
+'
,['+F_102+'库存]=sum(case F_102 when '''+F_102+''' then FinvQty else 0 end)'
from 表 group by F_102
exec('select Fnumber,单价=avg(Fsaleprice)'+@s+'
,办事处销售=sum(FsaleQty),办事处库存=sum(FinvQty)
from 表 group by Fnumber')

Answer 5

--测试--测试数据
create table 表(Fnumber char(17),Fsaleprice decimal(20,10),FsaleQty decimal(20,10),FinvQty decimal(20,10),F_102 varchar(10))
insert 表 select '9.03.J2738BZ30221',.0000000000,2.0000000000,.000000000,'吉林'
union all select '9.03.J2738BZ30221',.0000000000,1.0000000000,.0000000000,'沈阳'
union all select '9.03.J2738BZ30221',.0000000000,1.0000000000,.0000000000,'四川'
union all select '9.03.J2738BZ30221',.0000000000,2.0000000000,.0000000000,'昆明'
union all select '9.03.J2738BZ30221',.0000000000,1.0000000000,.0000000000,'内蒙'
union all select '9.03.J2738BZ30221',.0000000000,2.0000000000,.0000000000,'兰州'
union all select '9.03.J2738BZ30221',.0000000000,1.0000000000,.0000000000,'沈阳'
union all select '9.03.J2738BZ30221',.0000000000,2.0000000000,.0000000000,'沈阳'
union all select '9.03.J2738BZ30221',.0000000000,1.0000000000,.0000000000,'山西'
union all select '9.03.J2738BZ30221',.0000000000,2.0000000000,.0000000000,'兰州'
union all select '9.03.J2738BZ30221',.0000000000,8.0000000000,.0000000000,'哈尔滨'
union all select '9.03.J2831XZ10420',.0000000000,3.0000000000,.0000000000,'贵阳'
union all select '9.03.J2831XZ10410',.0000000000,3.0000000000,.0000000000,'贵阳'
union all select '9.03.J2832XZ10410',.0000000000,2.0000000000,.0000000000,'贵阳'
union all select '9.03.J2832XZ10420',.0000000000,2.0000000000,.0000000000,'贵阳'
union all select '9.03.J2832XZ10421',.0000000000,1.0000000000,.0000000000,'贵阳'
union all select '9.01.G2303XZ80220',55.0000000000,1.0000000000,.0000000000,'福建'
union all select '9.01.G2303XZ80210',55.0000000000,2.0000000000,.0000000000,'福建'
union all select '9.01.P0501XZ10620',158.0000000000,2.0000000000,.0000000000,'福建'
union all select '9.01.P0501XZ10610',158.0000000000,2.0000000000,.0000000000,'福建'
union all select '9.01.P0501XZ10600',.0000000000,1.0000000000,.0000000000,'福建'
godeclare @s varchar(8000)
set @s=''
select @s=@s+'
,['+F_102+'销售]=sum(case F_102 when '''+F_102+''' then FsaleQty else 0 end)'
+'
,['+F_102+'库存]=sum(case F_102 when '''+F_102+''' then FinvQty else 0 end)'
from 表 group by F_102
exec('select Fnumber,单价=avg(Fsaleprice)'+@s+'
,办事处销售=sum(FsaleQty),办事处库存=sum(FinvQty)
from 表 group by Fnumber')
go--删除测试
drop table 表/*--测试结果9.01.G2303XZ80210 55.0000000000 2.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 2.0000000000 .0000000000
9.01.G2303XZ80220 55.0000000000 1.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 1.0000000000 .0000000000
9.01.P0501XZ10600 .0000000000 1.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 1.0000000000 .0000000000
9.01.P0501XZ10610 158.0000000000 2.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 2.0000000000 .0000000000
9.01.P0501XZ10620 158.0000000000 2.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 2.0000000000 .0000000000
9.03.J2738BZ30221 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 8.0000000000 .0000000000 2.0000000000 .0000000000 2.0000000000 .0000000000 4.0000000000 .0000000000 1.0000000000 .0000000000 1.0000000000 .0000000000 4.0000000000 .0000000000 1.0000000000 .0000000000 23.0000000000 .0000000000
9.03.J2831XZ10410 .0000000000 .0000000000 .0000000000 3.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 3.0000000000 .0000000000
9.03.J2831XZ10420 .0000000000 .0000000000 .0000000000 3.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 3.0000000000 .0000000000
9.03.J2832XZ10410 .0000000000 .0000000000 .0000000000 2.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 2.0000000000 .0000000000
9.03.J2832XZ10420 .0000000000 .0000000000 .0000000000 2.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 2.0000000000 .0000000000
9.03.J2832XZ10421 .0000000000 .0000000000 .0000000000 1.0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 .0000000000 1.0000000000 .0000000000
--*/

Answer 6

在F_102里面:
有很多区域-包括总仓,分仓.其他各个仓库(贵阳.沈阳,等等...)
然后:办事处就是求除了（ 总仓,分仓，其他） 以外仓库的销售和库存之总合,然后有两列:
分别表示分仓和总仓的库存,最有要有一列表示全国总计库存(即办事处和分仓总仓的库存总合)
我刚接手这个，而且真正用SqL不到一个月。做不出了就玩完了：（

Answer 7

declare @s varchar(8000)
set @s=''
select @s=@s+'
,['+F_102+'销售]=sum(case F_102 when '''+F_102+''' then FsaleQty else 0 end)'
+'
,['+F_102+'库存]=sum(case F_102 when '''+F_102+''' then FinvQty else 0 end)'
from 表 group by F_102
exec('select Fnumber,单价=avg(Fsaleprice)'+@s+'
into #ryk   --  可以吗？？？？？
from 表 group by Fnumber')

Answer 8

declare @s varchar(8000)
set @s=''
select @s=@s+'
,['+F_102+'销售]=sum(case F_102 when '''+F_102+''' then FsaleQty else 0 end)'
+'
,['+F_102+'库存]=sum(case F_102 when '''+F_102+''' then FinvQty else 0 end)'
from 表 group by F_102
exec('select Fnumber,单价=avg(Fsaleprice)'+@s+'
into ##ryk   --用全局的才可以
from 表 group by Fnumber')

Answer 9

感谢zjcxc(: 邹建 :) 大哥~~~这100分先给你
明天再给你100！！！