问题求助:统计实例。

[老七]

表结构：
日期 ，物料，供应商，数量，价格，金额。
求统计SQL.
格式要求：
物料 供应商 累计数量 01数量 ...... 12数量
w1 aa 12 1 1
bb 10 1 1
小计 22 2 2
w1 aa 12 1 1
bb 10 1 1
cc 10 1 1
小计 32 3 3

………

合计 999 ... ...

[gc_ding]

价格有什么问题？说出来看看

[老七]

create table biao(物料 Nvarchar(10),供应商 Nvarchar(10),日期 datetime,数量 decimal(10,2),金额 decimal(10,2))
insert into biao3 select N'硝酸',N'张三','2005-12-01',1,1000
union all select N'盐酸',N'张三','2005-01-02',2,150.00
union all select N'盐酸',N'李四','2005-12-02',3,160.00
union all select N'硫酸',N'王五','2005-12-02',1,100.00
union all select N'硝酸',N'张三','2005-01-02',2,400.00
union all select N'硝酸',N'张三','2005-02-02',2,400.00
union all select N'硝酸',N'张三','2005-03-02',2,400.00
union all select N'硝酸',N'张三','2005-04-02',2,400.00
union all select N'硝酸',N'张三','2005-05-02',2,400.00
union all select N'硝酸',N'张三','2005-06-02',2,400.00
union all select N'硝酸',N'张三','2005-07-02',2,400.00
union all select N'硝酸',N'张三','2005-08-02',2,400.00
union all select N'硝酸',N'张三','2005-09-02',2,400.00
union all select N'硝酸',N'张三','2005-10-02',2,400.00
union all select N'硝酸',N'张三','2005-11-02',2,400.00
union all select N'硝酸',N'张三','2005-12-02',2,400.00

declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @month int
set @sql1='select isnull(物料,''合计'') as 物料,
case when 物料 is not null then isnull(供应商,''小计'') else 供应商 end as 供应商,sum(数量)as 累计数量,
sum(金额)as 累计金额,round(case when sum(数量)>0 then sum(金额)/sum(数量) else 0 end,2) as 累计价格'
set @sql2=''
set @month=1
while @month<=12
begin
set @sql2=@sql2+',sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end) as ['+cast(@month as varchar(10))+'数量],
sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end) as ['+cast(@month as varchar(10))+'金额],
round(sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end)
/ sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end),2)as ['+cast(@month as varchar(10))+'单价] '
set @month=@month+1
end
set @sql2=@sql1+@sql2+'from tb group by 物料,供应商 with rollup'
exec(@sql2)

[老七]

次序还有问题

[gc_ding]

是不是先把数量排完，再排金额，再排单价？

[老七]

yes

[gc_ding]

--拆开即可
create table biao(物料 Nvarchar(10),供应商 Nvarchar(10),日期 datetime,数量 decimal(10,2),金额 decimal(10,2))
insert into biao select N'硝酸',N'张三','2005-12-01',1,1000
union all select N'盐酸',N'张三','2005-01-02',2,150.00
union all select N'盐酸',N'李四','2005-12-02',3,160.00
union all select N'硫酸',N'王五','2005-12-02',1,100.00
union all select N'硝酸',N'张三','2005-01-02',2,400.00
union all select N'硝酸',N'张三','2005-02-02',2,400.00
union all select N'硝酸',N'张三','2005-03-02',2,400.00
union all select N'硝酸',N'张三','2005-04-02',2,400.00
union all select N'硝酸',N'张三','2005-05-02',2,400.00
union all select N'硝酸',N'张三','2005-06-02',2,400.00
union all select N'硝酸',N'张三','2005-07-02',2,400.00
union all select N'硝酸',N'张三','2005-08-02',2,400.00
union all select N'硝酸',N'张三','2005-09-02',2,400.00
union all select N'硝酸',N'张三','2005-10-02',2,400.00
union all select N'硝酸',N'张三','2005-11-02',2,400.00
union all select N'硝酸',N'张三','2005-12-02',2,400.00

declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @sql4 varchar(8000)
declare @sql5 varchar(8000)
declare @month int
set @sql1='select isnull(物料,''合计'') as 物料,
case when 物料 is not null then isnull(供应商,''小计'') else 供应商 end as 供应商,sum(数量)as 累计数量,
sum(金额)as 累计金额,round(case when sum(数量)>0 then sum(金额)/sum(数量) else 0 end,2) as 累计价格'
set @sql2=''
set @sql3=''
set @sql4=''
set @sql5=''
set @month=1
while @month<=12
begin
set @sql2=@sql2+',sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end) as ['+cast(@month as varchar(10))+'数量]'
set @month=@month+1
end
set @month=1
while @month<=12
begin
set @sql3=@sql3+',sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end) as ['+cast(@month as varchar(10))+'金额]'
set @month=@month+1
end
set @month=1
while @month<=12
begin
set @sql4=@sql4+', round(sum(case when month(日期)='
+cast(@month as varchar(10))+'then 数量 end)
/ sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end),2)as ['+cast(@month as varchar(10))+'单价] '
set @month=@month+1
end
set @sql5=@sql1+@sql2+@sql3+@sql4+'from biao group by 物料,供应商 with rollup'
exec(@sql5)

[老七]

价格是金额/数量，
set @sql4=@sql4+', round(sum(case when month(日期)='
+cast(@month as varchar(10))+'then 数量 end)
/ sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end),2)as ['+cast(@month as varchar(10))+'单价] '
明天结帖

[老七]

回复一定要合要求，数量，金额，单价的1-12月及总计要都列出来。

[老七]

gc_ding(E.T) 把问题想得简单了一点，单价在查询时不好实现。

[gc_ding]

那你要怎么显示？

[老七]

物料 供应商 全年数量 01数量 ... 11数量 12数量 全年金额 01金额 ... 11金额 12金额 全年单价 01单价 ... 11单价 12单价

[gc_ding]

不会是这样吧
-------------
物料 供应商 累计数量 累计金额 累计单价 1数量...12数量 1金额...12金额 1单价...12单价
w1 aa
w1 bb
小计
w2 aa
w2 bb
小计
合计

[老七]

对！
我应用中的实际语句：
Declare @I Int
Declare @S Nvarchar(4000)
Declare @S1 Nvarchar(4000)
Declare @S2 Nvarchar(4000)
Select @S='',@I=1

While @I<=12
Begin
Select @S=@S+', SUM(Case month([FDate]) When '+Rtrim(@I)+' Then FInQty Else 0 End) As ['+Right(100+@I,2)+N'月数量]'
Select @I=@I+1
End
select @S=',SUM([FInQty]) As 数量总计'+@S+',SUM([FAllAmount]) As 金额总计'
select @I=1
While @I<=12
Begin
Select @S=@S+', SUM(Case Month([FDate]) When '+Rtrim(@I)+' Then FAllAmount Else 0 End) As ['+Right(100+@I,2)+N'月金额]'
Select @I=@I+1
End
Select @S=N'Select IsNull(FMatFullNumber,N''总计'') As 物料,(Case When Grouping(FMatFullNumber)=1 Then '''' When Grouping(FSupplyFullNumber)=1 Then N''合计'' Else FSupplyFullNumber End ) As 供应商'+@S+N' into qrz From #T116_1 Group By FMatFullNumber,FSupplyFullNumber with rollup'
EXEC(@S)

加入价格就感觉非常困难了。

[gc_ding]

--楼主，我还没看你写的
--我上面的改一下你再看
...
--执行查询
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @month int
set @sql1='select isnull(物料,''合计'') as 物料,
case when 物料 is not null then isnull(供应商,''小计'') else 供应商 end
as 供应商,sum(数量)as 累计数量,sum(金额)as 累计金额,sum(价格)as 累计价格'
set @sql2=''
set @month=1
while @month<=12
begin
set @sql2=@sql2+',sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end) as ['+cast(@month as varchar(10))+'数量],
sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end) as ['+cast(@month as varchar(10))+'金额],
sum(case when month(日期)='+cast(@month as varchar(10))+'
then 价格 end) as ['+cast(@month as varchar(10))+'价格] '
set @month=@month+1
end
set @sql2=@sql1+@sql2+'from tb group by 物料,供应商 with rollup'
exec(@sql2)
...

[老七]

?sum(价格）
价格是金额/数量,......

[gc_ding]

你那个价格不是“单价”么?

[sky266]

mark 有点意思

[gc_ding]

--楼主搞定了，汗...
--建立测试数据
if object_id('tb') is not null drop table tb
if object_id('testdb') is not null drop table testdb
create table tb(日期 datetime, 物料 varchar(100),
供应商 varchar(100), 数量 int, 价格 real, 金额 real)
insert tb
select '2006-11-10','w1','aa',1100,10.5,130 union all
select '2006-11-10','w1','bb',1110,10.5,230 union all
select '2006-11-20','w2','aa',1120,10.5,330 union all
select '2006-11-20','w2','bb',1130,10.5,430 union all
select '2006-12-10','w1','aa',1140,10.5,530 union all
select '2006-12-10','w1','bb',1150,10.5,630 union all
select '2006-12-20','w2','aa',1160,10.5,730 union all
select '2006-12-20','w2','bb',1170,10.5,830

--执行查询
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @month int
set @sql1='select isnull(物料,''合计'') as 物料,
case when 物料 is not null then isnull(供应商,''小计'') else 供应商 end as
供应商,sum(数量)as 累计数量,sum(金额)as 累计金额,cast(0.0 as real) as 累计价格'
set @sql2=''
set @month=1
while @month<=12
begin
set @sql2=@sql2+',sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end) as ['+cast(@month as varchar(10))+'数量],
sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end) as ['+cast(@month as varchar(10))+'金额],
round(sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end)
/ sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end),2) as ['+cast(@month as varchar(10))+'单价] '
set @month=@month+1
end
set @sql2=@sql1+@sql2+'into testdb from tb group by 物料,供应商 with rollup'
exec(@sql2)
set @sql3=''
select @sql3=@sql3+'+isnull(['+name+'],0)'
from syscolumns where name like N'%单价'
set @sql3='update testdb set 累计价格='+stuff(@sql3,1,1,'')+';select * from testdb'
exec(@sql3)

--删除测试环境
drop table tb, testdb

--查看结果
/*
物料 供应商 累计数量 累计金额 累计价格 1数量 1金额 1单价... 11数量 11金额 11单价 12数量 12金额 12单价
w1 aa 2240 660 10.61 1100 130 8.46 1140 530 2.15
w1 bb 2260 860 6.66 1110 230 4.83 1150 630 1.83
小计 4500 1520 8.12 2210 360 6.13 2290 1160 1.97
w2 aa 2280 1060 4.98 1120 330 3.39 1160 730 1.59
w2 bb 2300 1260 4.04 1130 430 2.63 1170 830 1.41
小计 4580 2320 4.45 2250 760 2.96 2330 1560 1.49
合计 9080 3840 5.67 4460 1120 3.98 4620 2720 1.7
*/

[gc_ding]

--查看结果
/*
物料 供应商 累计数量 累计金额 累计价格...11数量 11金额 11单价 12数量 12金额 12单价
w1 aa 2240 660 10.61 1100 130 8.46 1140 530 2.15
w1 bb 2260 860 6.66 1110 230 4.83 1150 630 1.83
小计 4500 1520 8.12 2210 360 6.13 2290 1160 1.97
w2 aa 2280 1060 4.98 1120 330 3.39 1160 730 1.59
w2 bb 2300 1260 4.04 1130 430 2.63 1170 830 1.41
小计 4580 2320 4.45 2250 760 2.96 2330 1560 1.49
合计 9080 3840 5.67 4460 1120 3.98 4620 2720 1.7
*/

[gc_ding]

--呵呵，金额/数量=单价
round(sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end)
/ sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end),2) as ['+cast(@month as varchar(10))+'单价]
--以上累计单价为把12个月的单价相加
--如果是全年金额/全年数量=全年单价,则直接这样就行
--执行查询
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @month int
set @sql1='select isnull(物料,''合计'') as 物料,
case when 物料 is not null then isnull(供应商,''小计'') else 供应商 end as 供应商,sum(数量)as 累计数量,
sum(金额)as 累计金额,round(case when sum(数量)>0 then sum(金额)/sum(数量) else 0 end,2) as 累计价格'
set @sql2=''
set @month=1
while @month<=12
begin
set @sql2=@sql2+',sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end) as ['+cast(@month as varchar(10))+'数量],
sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end) as ['+cast(@month as varchar(10))+'金额],
round(sum(case when month(日期)='+cast(@month as varchar(10))+'
then 数量 end)
/ sum(case when month(日期)='+cast(@month as varchar(10))+'
then 金额 end),2)as ['+cast(@month as varchar(10))+'单价] '
set @month=@month+1
end
set @sql2=@sql1+@sql2+'from tb group by 物料,供应商 with rollup'
exec(@sql2)