一年的数据统计分析~~~在线等~~~谢谢

[llh6795]

按月建表
GZ200801
GZ200802
....
GZ200812

例：GZ200801
xm ... sfxj
aaa 1500
bbb 1600
ccc 1800

现在要求出一张12个月的分析表，格式如下：
xm 1月 2月 3月 ... 10月 11月 12月 合计
aaa 1500 ...
bbb 1600
ccc 1800

应该怎么处理，请大家帮忙看看，或者给个思路，谢谢！

[jlwei888]

select 1.xm,1.sfxj as 1月,2.sfxj as 2月,.....
from
(select xm,sfxj from GZ200801 ) as 1,
(select xm,sfxj from GZ200802 ) as 2,
(select xm,sfxj from GZ200803 ) as 3,
...
where 1.xm = 2.xm ....

[让你望见影子的墙]

select xm,一月=sum(case when 月份=1 then sfxj end),……
from
(select xm   , sfxj,月份=1
from  GZ200801 
union all
select xm   , sfxj,月份=2
from  GZ200802 
……

select xm   , sfxj,月份=12
from  GZ2008012 

order by xM) K
group by xm

1楼的临时表是不能使用数字的

[昵称被占用了]

select xm,sum([1月]) as [1月],sum([2月]) as [2月],sum([3月]) as [3月],...,sum([12月]) as [12月],sum(合计) as 合计
from (
select xm,sfxj as [1月],0 as [2月],0 as [3月],  ...  ,0 as [12月],sfxj as 合计 from GZ200801
union all
select xm,0 as [1月],sfxj as [2月],0 as [3月],  ...  ,0 as [12月],sfxj as 合计 from GZ200802
union all
select xm,0 as [1月],0 as [2月],sfxj as [3月],  ...  ,0 as [12月],sfxj as 合计 from GZ200803
...
union all
select xm,0 as [1月],0 as [2月],0 as [3月],  ...  ,sfxj as [12月],sfxj as 合计 from GZ200812
) as t
group by xm

[水族杰纶]

--如果格式固定則可以
set nocount on
if object_id('GZ200801')is not null drop table  GZ200801
go
create table GZ200801 (xm varchar(10),  sfxj int) 
insert GZ200801 select 'aaa'       ,       1500 
insert GZ200801 select 'bbb'       ,       1600 
insert GZ200801 select 'ccc'         ,     1800 
if object_id('GZ200802')is not null drop table  GZ200802
go
create table GZ200802 (xm varchar(10),  sfxj int) 
insert GZ200802 select 'aaa'       ,       2500 
insert GZ200802 select 'bbb'       ,       3600 
insert GZ200802 select 'ccc'         ,     4800 
if object_id('GZ200803')is not null drop table  GZ200803
go
create table GZ200803 (xm varchar(10),  sfxj int) 
insert GZ200803 select 'aaa'       ,       22500 
insert GZ200803 select 'bbb'       ,       13600 
insert GZ200803 select 'ccc'         ,     44800 
select t1.xm,t1.sfxj as [1月],t2.sfxj as [2月],t3.sfxj as [3月] from GZ200801 t1 inner join GZ200802 t2 on t1.xm=t2.xm
                                                                                                                               inner join GZ200803 t3 on t1.xm=t3.xm
/*xm         1月          2月          3月          
---------- ----------- ----------- ----------- 
aaa        1500        2500        22500
bbb        1600        3600        13600
ccc        1800        4800        44800*/

[llh6795]

谢谢楼上的朋友

[hyde100]

select
	t.xm,
	g1.sfxj  '1月',
	g2.sfxj  '2月',
	g3.sfxj  '3月',
	g4.sfxj  '4月',
	g5.sfxj  '5月',
	g6.sfxj  '6月',
	g7.sfxj  '7月',
	g8.sfxj  '8月',
	g9.sfxj  '9月',
	g10.sfxj  '10月',
	g11.sfxj  '11月',
	g12.sfxj  '12月'
from
(
	select distinct  xm from GZ200801 union 
	select distinct  xm from GZ200802 union 
	select distinct  xm from GZ200803 union 
	select distinct  xm from GZ200804 union 
	select distinct  xm from GZ200805 union 
	select distinct  xm from GZ200806 union 
	select distinct  xm from GZ200807 union 
	select distinct  xm from GZ200808 union 
	select distinct  xm from GZ200809 union 
	select distinct  xm from GZ2008010 union 
	select distinct  xm from GZ2008011 union 
	select distinct  xm from GZ2008012
)t
 left join GZ200801 g1 on g1.xm=t.xm
 left join GZ200802 g2 on g2.xm=t.xm
 left join GZ200803 g3 on g3.xm=t.xm
 left join GZ200804 g4 on g4.xm=t.xm
 left join GZ200805 g5 on g5.xm=t.xm
 left join GZ200806 g6 on g6.xm=t.xm
 left join GZ200807 g7 on g7.xm=t.xm
 left join GZ200808 g8 on g8.xm=t.xm
 left join GZ200809 g9 on g9.xm=t.xm
 left join GZ2008010 g10 on g10.xm=t.xm
 left join GZ2008011 g11 on g11.xm=t.xm
 left join GZ2008012 g12 on g12.xm=t.xm

[水族杰纶]

set nocount on
if object_id('GZ200801')is not null drop table  GZ200801
go
create table GZ200801 (xm varchar(10),  sfxj int) 
insert GZ200801 select 'aaa'       ,       1500 
insert GZ200801 select 'bbb'       ,       1600 
insert GZ200801 select 'ccc'         ,     1800 
if object_id('GZ200802')is not null drop table  GZ200802
go
create table GZ200802 (xm varchar(10),  sfxj int) 
insert GZ200802 select 'aaa'       ,       2500 
insert GZ200802 select 'bbb'       ,       3600 
insert GZ200802 select 'ccc'         ,     4800 
if object_id('GZ200803')is not null drop table  GZ200803
go
create table GZ200803 (xm varchar(10),  sfxj int) 
insert GZ200803 select 'aaa'       ,       22500 
insert GZ200803 select 'bbb'       ,       13600 
insert GZ200803 select 'ccc'         ,     44800 
select *,[1月]+ [2月]+ [3月] as 合計 from (
select t1.xm,t1.sfxj as [1月],t2.sfxj as [2月],t3.sfxj as [3月] from GZ200801 t1 inner join GZ200802 t2 on t1.xm=t2.xm
                                                                                                                               inner join GZ200803 t3 on t1.xm=t3.xm)t
/*xm         1月          2月          3月          合計          
---------- ----------- ----------- ----------- ----------- 
aaa        1500        2500        22500       26500
bbb        1600        3600        13600       18800
ccc        1800        4800        44800       51400
*/

[llh6795]

谢谢大家了，基本上出来了