年度合计的问题 我知道对高手很简单

Question

现在有4个表
1.分类表      (分类ID, 分类名称)
2.物资基础表  (物资ID, 物资名称,所属分类ID)
3.物资采购表  (ID, 物资ID, 数量, 价格, 采购时间)
4.物资出货表  (ID, 物资ID, 数量, 价格, 出货时间)具体数据结构比较多,但最关键的就是这样,最终统计结果表是这样的
==========================================
                 XXXX年进出金额统计月份    采购     出货   本月合计
一月     0       0      0
二月     0       0      0
三月     0       0      0
四月     0       0      0
五月     0       0      0
六月     0       0      0
七月     0       0      0
八月     0       0      0
九月     0       0      0
十月     0       0      0
十一月   0       0      0
十二月   0       0      0
本类合计 0       0      0
============================================这个统计结果SQL的查询调价基本有三个,请高手们发挥一下,写个有效的SQL,数据量可能比较大
1 获得某年的统计结果
2.获得某年某分类的统计结果
3.获得某您某种物资的统计结果存在的问题,比如某个月没有采购也没有出货,那么SQL的统计中可能就不显示该月,我想能不能没有的月份自动设为0

Answer 1

select 
年份,月份,
[采购]=sum(case when flag=0 then 金额 else 0 end),
[出货]=sum(case when flag=1 then 金额 else 0 end),
[本月合计]=sum(金额)
from 
(select sum(数量*价格) as 金额, 月份=month(采购时间),年份=year(采购时间),flag=0
from  物资采购表  group by year(采购时间),month(采购时间)
union all
select  sum(数量*价格) as 金额, 月份=month(出货时间),年份=year(出货时间),flag=1
from  物资出货表  group by year(出货时间),month(出货时间)
)T
group by 年份,月份

Answer 2

--只好卖劳力，月份没有转成大写 
select (case when A.m=13 then '本类合计' else rtrim(A.m)+'月' end) 月份,isnull(B.采购,0)采购,isnull(C.出货,0)出货,(isnull(C.出货,0)-isnull(B.采购,0))本月合计 
from (select top 13 langid+1 m from master.dbo.syslanguages order by langid)A
left join(select month(采购时间)月份,sum(数量*价格)采购 from 物资采购表 group by month(采购时间) with rollup)B
on A.m=isnull(B.月份,13)
left join(select month(出货时间)月份,sum(数量*价格)出货 from 物资出货表 group by month(出货时间) with rollup)C
on A.m=isnull(C.月份,13)select (case when A.m=13 then '本类合计' else rtrim(A.m)+'月' end) 月份,isnull(B.采购,0)采购,isnull(C.出货,0)出货,(isnull(C.出货,0)-isnull(B.采购,0))本月合计 
from (select top 13 langid+1 m from master.dbo.syslanguages order by langid)A
left join(select month(采购时间)月份,sum(数量*价格)采购 from 物资采购表 AA,物资基础表 BB,分类表 CC where AA.物资ID=BB.物资ID and BB.所属分类ID=CC.分类ID and CC.分类名称='线类' group by month(采购时间) with rollup)B
on A.m=isnull(B.月份,13)
left join(select month(出货时间)月份,sum(数量*价格)出货 from 物资出货表 AA,物资基础表 BB,分类表 CC where AA.物资ID=BB.物资ID and BB.所属分类ID=CC.分类ID and CC.分类名称='线类' group by month(出货时间) with rollup)C
on A.m=isnull(C.月份,13)select (case when A.m=13 then '本类合计' else rtrim(A.m)+'月' end) 月份,isnull(B.采购,0)采购,isnull(C.出货,0)出货,(isnull(C.出货,0)-isnull(B.采购,0))本月合计 
from (select top 13 langid+1 m from master.dbo.syslanguages order by langid)A
left join(select month(采购时间)月份,sum(数量*价格)采购 from 物资采购表 where 物资ID=(select 物资ID from 物资基础表 where 物资名称='米线') group by month(采购时间) with rollup)B
on A.m=isnull(B.月份,13)
left join(select month(出货时间)月份,sum(数量*价格)出货 from 物资出货表 where 物资ID=(select 物资ID from 物资基础表 where 物资名称='米线')group by month(出货时间) with rollup)C
on A.m=isnull(C.月份,13)

Answer 3

--忘了年
select (case when A.m=13 then '本类合计' else rtrim(A.m)+'月' end) 月份,isnull(B.采购,0)采购,isnull(C.出货,0)出货,(isnull(C.出货,0)-isnull(B.采购,0))本月合计 
from (select top 13 langid+1 m from master.dbo.syslanguages order by langid)A
left join(select month(采购时间)月份,sum(数量*价格)采购 from 物资采购表 where year(采购时间)='2008' group by month(采购时间) with rollup)B
on A.m=isnull(B.月份,13)
left join(select month(出货时间)月份,sum(数量*价格)出货 from 物资出货表 where year(出货时间)='2008' group by month(出货时间) with rollup)C
on A.m=isnull(C.月份,13)select (case when A.m=13 then '本类合计' else rtrim(A.m)+'月' end) 月份,isnull(B.采购,0)采购,isnull(C.出货,0)出货,(isnull(C.出货,0)-isnull(B.采购,0))本月合计 
from (select top 13 langid+1 m from master.dbo.syslanguages order by langid)A
left join(select month(采购时间)月份,sum(数量*价格)采购 from 物资采购表 AA,物资基础表 BB,分类表 CC where AA.物资ID=BB.物资ID and BB.所属分类ID=CC.分类ID and year(采购时间)='2008' and CC.分类名称='线类' group by month(采购时间) with rollup)B
on A.m=isnull(B.月份,13)
left join(select month(出货时间)月份,sum(数量*价格)出货 from 物资出货表 AA,物资基础表 BB,分类表 CC where AA.物资ID=BB.物资ID and BB.所属分类ID=CC.分类ID and year(出货时间)='2008' and CC.分类名称='线类' group by month(出货时间) with rollup)C
on A.m=isnull(C.月份,13)select (case when A.m=13 then '本类合计' else rtrim(A.m)+'月' end) 月份,isnull(B.采购,0)采购,isnull(C.出货,0)出货,(isnull(C.出货,0)-isnull(B.采购,0))本月合计 
from (select top 13 langid+1 m from master.dbo.syslanguages order by langid)A
left join(select month(采购时间)月份,sum(数量*价格)采购 from 物资采购表 where year(采购时间)='2008' and 物资ID=(select 物资ID from 物资基础表 where 物资名称='米线') group by month(采购时间) with rollup)B
on A.m=isnull(B.月份,13)
left join(select month(出货时间)月份,sum(数量*价格)出货 from 物资出货表 where year(出货时间)='2008' and 物资ID=(select 物资ID from 物资基础表 where 物资名称='米线')group by month(出货时间) with rollup)C
on A.m=isnull(C.月份,13)

Answer 4

是可以统计出来了,不过对于没有记录的月份,年,月一项就是NULL了能不能显示出为0的月份?

Answer 5

--我的能显示
1月 .0000 .0000 .0000
2月 12000.0000 .0000 -12000.0000
3月 .0000 .0000 .0000
4月 .0000 .0000 .0000
5月 .0000 .0000 .0000
6月 100.0000 120.0000 20.0000
7月 .0000 .0000 .0000
8月 .0000 .0000 .0000
9月 .0000 .0000 .0000
10月 .0000 .0000 .0000
11月 .0000 .0000 .0000
12月 480.0000 .0000 -480.0000
本类合计 12580.0000 120.0000 -12460.0000
--
我用的表
create table 分类表(分类ID int identity(1,1),分类名称 varchar(20)) 
create table 物资基础表(物资ID int identity(1,1),物资名称 varchar(20),所属分类ID int) 
create table 物资采购表(ID int identity(1,1),物资ID int, 数量 int, 价格 money,采购时间 datetime) 
create table 物资出货表(ID int identity(1,1),物资ID int, 数量 int, 价格 money,出货时间 datetime) 
insert 分类表(分类名称) select '线类'
insert 分类表(分类名称) select '布类'
select * from 分类表
insert 物资基础表(物资名称,所属分类ID) select '电线',1
insert 物资基础表(物资名称,所属分类ID) select '米线',1
insert 物资基础表(物资名称,所属分类ID) select '妮子',2
insert 物资基础表(物资名称,所属分类ID) select '光线',1
insert 物资基础表(物资名称,所属分类ID) select '的确凉',2
select * from 物资基础表
insert 物资采购表(物资ID,数量,价格,采购时间) select 2,20,5,'2008-06-06'
insert 物资采购表(物资ID,数量,价格,采购时间) select 1,40,12,'2008-12-06'
insert 物资采购表(物资ID,数量,价格,采购时间) select 3,1000,12,'2008-2-06'
select * from 物资采购表
insert 物资出货表(物资ID,数量,价格,出货时间) select 2,24,5,'2008-06-07'
select * from 物资出货表

Answer 6

wgzaaa 你写的怎么这么长啊我有点看不懂了

Answer 7

效率应该还可以，可以等效率更高的，本月合计我用了isnull(C.出货,0)-isnull(B.采购,0)，即 出货金额-采购金额，如果不是你的意思，可按你的意思改改，三段改的一样

Answer 8

select top 13 langid+1 m from master.dbo.syslanguages order by langid这句俺看不懂,是否可以不要?
只要有月份的数字就OK,程序中会进行相应大写转换

Answer 9

select top 13 langid+1 m from master.dbo.syslanguages order by langid 
是利用系统表syslanguages 在master库中，查出1－13来，不要没有数据的月就显示不出来

Answer 10

 程序中会进行相应大写转换可以将这句
(case when A.m=13 then '本类合计' else rtrim(A.m)+'月' end) 月份
改为:A.m 月份－－整数,然后在程序中调整一下:13对应'本类合计'
或者改为:(case when A.m=13 then '本类合计' else rtrim(A.m) end) 月份

Answer 11

wgzaaa 你好,再请教一个问题出货表中实际上还有一个关键字段 出货类型(1普通 2调拨)出货包含调拨,只是需要顺便统计出调拨数
============================
月份  采购   出货(调拨数)   合计
==============================我想是不是可以直接在这句上修改
left join(select month(出货时间)月份,sum(数量*价格)出货 from 物资出货表 where year(出货时间)='2008' group by month(出货时间) with rollup)C

Answer 12

--改第一个吧，第一行要改第二个left join要改其它不动
select (case when A.m=13 then '本类合计' else rtrim(A.m)+'月' end) 月份,isnull(B.采购,0)采购,cast(isnull(C.出货,0) as varchar(20))+'('+rtrim(isnull(调拨数,0))+')' [出货(调拨数)],(isnull(C.出货,0)-isnull(B.采购,0))本月合计 
from (select top 13 langid+1 m from master.dbo.syslanguages order by langid)A
left join(select month(采购时间)月份,sum(数量*价格)采购 from 物资采购表 where year(采购时间)='2008' group by month(采购时间) with rollup)B
on A.m=isnull(B.月份,13)
left join(select month(出货时间)月份,sum(数量*价格)出货 ,sum(case 出货类型 when 2 then 数量 else 0 end)调拨数 from 物资出货表 where year(出货时间)='2008' group by month(出货时间) with rollup)C
on A.m=isnull(C.月份,13)
--------------
月份 采购  出货(调拨数) 本月合计
1月 .0000 0.00(0) .0000
2月 12000.0000 0.00(0) -12000.0000
3月 .0000 0.00(0) .0000
4月 .0000 0.00(0) .0000
5月 .0000 0.00(0) .0000
6月 100.0000 180.00(24) 80.0000
7月 .0000 0.00(0) .0000
8月 .0000 0.00(0) .0000
9月 .0000 0.00(0) .0000
10月 .0000 0.00(0) .0000
11月 .0000 0.00(0) .0000
12月 480.0000 0.00(0) -480.0000
本类合计 12580.0000 180.00(24) -12400.0000