求一条年度统计语句

Question

表：
id       month       fen
1        2009-1       85
2        2009-1       70
1        2009-2        70
2        2009-2       80
1        2009-3       90要得到下表
 id     1      2      3      avg
1       85     70     90     81.67
2       70     80     0       50这个表要得出一年12个月的数据。请各位大侠帮忙！

Answer 1

select id,
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 1 then fen then 0 end)  [1],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 2 then fen then 0 end)  [2],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 3 then fen then 0 end)  [3],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 4 then fen then 0 end)  [4],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 5 then fen then 0 end)  [5],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 6 then fen then 0 end)  [6],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 7 then fen then 0 end)  [7],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 8 then fen then 0 end)  [8],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 9 then fen then 0 end)  [9],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 10 then fen then 0 end) [10],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 11 then fen then 0 end) [11],
  sum(case substring([month],charindex('-',[month]) + 1 , len(month)) when 12 then fen then 0 end) [12],
  avg(fen*1.0) [avg]
from tb
group by id

Answer 2

create table tb(id int,     [month] varchar(10),      fen int)
insert into tb values(1 ,       '2009-1' ,     85) 
insert into tb values(2 ,       '2009-1' ,     70) 
insert into tb values(1 ,       '2009-2' ,       70) 
insert into tb values(2 ,       '2009-2' ,     80) 
insert into tb values(1 ,       '2009-3' ,     90) 
goselect id,
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '1' then fen else 0 end)  [1],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '2' then fen else 0 end)  [2],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '3' then fen else 0 end)  [3],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '4' then fen else 0 end)  [4],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '5' then fen else 0 end)  [5],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '6' then fen else 0 end)  [6],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '7' then fen else 0 end)  [7],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '8' then fen else 0 end)  [8],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '9' then fen else 0 end)  [9],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '10' then fen else 0 end) [10],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '11' then fen else 0 end) [11],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '12' then fen else 0 end) [12],
  avg(fen*1.0) [avg]
from tb
group by iddrop table tb/*
id          1           2           3           4           5           6           7           8           9           10          11          12          avg                                      
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------- 
1           85          70          90          0           0           0           0           0           0           0           0           0           81.666666
2           70          80          0           0           0           0           0           0           0           0           0           0           75.000000（所影响的行数为 2 行）*/

Answer 3

create table tb(id int,     [month] varchar(10),      fen int)
insert into tb values(1 ,       '2009-1' ,     85) 
insert into tb values(2 ,       '2009-1' ,     70) 
insert into tb values(1 ,       '2009-2' ,       70) 
insert into tb values(2 ,       '2009-2' ,     80) 
insert into tb values(1 ,       '2009-3' ,     90) 
go--如果都是2009年的。
select id,
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '1' then fen else 0 end)  [1],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '2' then fen else 0 end)  [2],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '3' then fen else 0 end)  [3],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '4' then fen else 0 end)  [4],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '5' then fen else 0 end)  [5],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '6' then fen else 0 end)  [6],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '7' then fen else 0 end)  [7],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '8' then fen else 0 end)  [8],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '9' then fen else 0 end)  [9],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '10' then fen else 0 end) [10],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '11' then fen else 0 end) [11],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '12' then fen else 0 end) [12],
  avg(fen*1.0) [avg]
from tb
group by id
/*
id          1           2           3           4           5           6           7           8           9           10          11          12          avg                                      
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------- 
1           85          70          90          0           0           0           0           0           0           0           0           0           81.666666
2           70          80          0           0           0           0           0           0           0           0           0           0           75.000000（所影响的行数为 2 行）*/--如果还有其他年份的.
select id , left([month],charindex('-',[month]) - 1) [year] ,
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '1' then fen else 0 end)  [1],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '2' then fen else 0 end)  [2],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '3' then fen else 0 end)  [3],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '4' then fen else 0 end)  [4],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '5' then fen else 0 end)  [5],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '6' then fen else 0 end)  [6],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '7' then fen else 0 end)  [7],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '8' then fen else 0 end)  [8],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '9' then fen else 0 end)  [9],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '10' then fen else 0 end) [10],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '11' then fen else 0 end) [11],
  sum(case substring([month],charindex('-',[month]) + 1 , len([month])) when '12' then fen else 0 end) [12],
  avg(fen*1.0) [avg]
from tb
group by id , left([month],charindex('-',[month]) - 1)
/*
id          year       1           2           3           4           5           6           7           8           9           10          11          12          avg                                      
----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------- 
1           2009       85          70          90          0           0           0           0           0           0           0           0           0           81.666666
2           2009       70          80          0           0           0           0           0           0           0           0           0           0           75.000000（所影响的行数为 2 行）
*/
drop table tb

Answer 4

create table #1(id int,  month nvarchar(20), fen float)
insert #1 values(1,        '2009-1',      85) 
insert #1 values(2,        '2009-1',      70) 
insert #1 values(1,        '2009-2',        70) 
insert #1 values(2,        '2009-2',      80) 
insert #1 values(1,        '2009-3',      90)select * from 
(
select id,substring(month,6,len(month)-5) as month ,fen 
from #1
union all
select id,'avg',avg(fen)
from #1
group by id) a
pivot
(
max(fen) for month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[avg])
)b
id          1                      2                      3                      4                      5                      6                      7                      8                      9                      10                     11                     12                     avg
----------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1           85                     70                     90                     NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   81.6666666666667
2           70                     80                     NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   75(2 row(s) affected)

Answer 5

select 
id
,isnull([1],0) [1]
,isnull([2],0) [2]
,isnull([3],0) [3]
,isnull([4],0) [4]
,isnull([5],0) [5]
,isnull([6],0) [6]
,isnull([7],0) [7]
,isnull([8],0) [8]
,isnull([9],0) [9]
,isnull([10],0) [10]
,isnull([11],0) [11]
,isnull([12],0) [12]
,[avg]
 from 
(
select id,substring(month,6,len(month)-5) as month ,fen 
from #1
union all
select id,'avg',avg(fen)
from #1
group by id) a
pivot
(
max(fen) for month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[avg])
)bid          1                      2                      3                      4                      5                      6                      7                      8                      9                      10                     11                     12                     avg
----------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1           85                     70                     90                     0                      0                      0                      0                      0                      0                      0                      0                      0                      81.6666666666667
2           70                     80                     0                      0                      0                      0                      0                      0                      0                      0                      0                      0                      75(2 row(s) affected)

Answer 6

create table #t (id int,month varchar(10) ,fen int)
insert #t values(1,'2009-1',85)
insert #t values(2,'2009-1',70)
insert #t values(1,'2009-2',70)
insert #t values(2,'2009-2',80)
insert #t values(1,'2009-3',90)with a as
(select id,left(month,4) as Year,
sum(case when right(month,len(month)-charindex('-',month))='1' then fen else 0 end) month1, 
sum(case when right(month,len(month)-charindex('-',month))='2' then fen else 0 end) month2, 
sum(case when right(month,len(month)-charindex('-',month))='3' then fen else 0 end) month3
from #T group by id,left(month,4))
select *,round(cast((month1+month2+month3) as float(2))/3,2) as [avg] from a/*
id          Year     month1      month2      month3      avg
----------- -------- ----------- ----------- ----------- ----------------------
1           2009     85          70          90          81.67
2           2009     70          80          0           50*/

Answer 7

select  id,SUM(aa) as '1',SUM(bb) as '2',SUM(cc) as '3',round(sum(aa+bb+cc)*1.0/COUNT(id),2) avg
from
(
select id,sum(case when right(m,1)='1' then  fen  else 0 end) as aa,
                sum(case when right(m,1)='2' then  fen  else 0 end) as bb,
               sum(case when right(m,1)='3' then  fen  else 0 end) as cc
from  table
group by id,m
)A 
group by id
order by id

Answer 8

test result id     1     2      3          avg
1 85 70 90 81.670000000000
2 70 80 0 75.000000000000

Answer 9

select id,left([month],4) 年分,
sum(case convert( int,stuff([month],1,5,'')) when 1 then fen  else 0 end ) [1月],
sum(case convert( int,stuff([month],1,5,'')) when 2 then fen  else 0 end ) [2月],
sum(case convert( int,stuff([month],1,5,'')) when 3 then fen  else 0 end ) [3月],
sum(case convert( int,stuff([month],1,5,'')) when 4 then fen  else 0 end ) [4月], 
        sum(case convert( int,stuff([month],1,5,'')) when 5 then fen  else 0 end ) [5月], 
        sum(case convert( int,stuff([month],1,5,'')) when 6 then fen  else 0 end ) [6月], 
        sum(case convert( int,stuff([month],1,5,'')) when 7 then fen  else 0 end ) [7月],  
        sum(case convert( int,stuff([month],1,5,'')) when 8 then fen  else 0 end ) [8月], 
        sum(case convert( int,stuff([month],1,5,'')) when 9 then fen  else 0 end ) [9月],
        sum(case convert( int,stuff([month],1,5,'')) when 10 then fen  else 0 end ) [10月], 
        sum(case convert( int,stuff([month],1,5,'')) when 11 then fen  else 0 end ) [11月], 
        sum(case convert( int,stuff([month],1,5,'')) when 12 then fen  else 0 end ) [12月],
        avg(fen*1.0) [平均]
from tb
group by id,left([month],4)

Answer 10

declare @sql varchar(32)
set @sql = '
select 
id
,isnull([1],0) [1]
,isnull([2],0) [2]
,isnull([3],0) [3]
,isnull([4],0) [4]
,isnull([5],0) [5]
,isnull([6],0) [6]
,isnull([7],0) [7]
,isnull([8],0) [8]
,isnull([9],0) [9]
,isnull([10],0) [10]
,isnull([11],0) [11]
,isnull([12],0) [12]
,[avg]
 from 
(
select id,substring(month,6,len(month)-5) as month ,fen 
    from #1 'exec ('select id,'avg',avg(fen)   from #1
    group by id) a pivot(
    max(fen) for month in ( ' + @sql + '))b'

Answer 11

if object_id('tb') is not null drop table tb
go
create table tb(id int,[month] varchar(10),fen int)
insert into tb values(1 ,       '2009-1' ,     85) 
insert into tb values(2 ,       '2009-1' ,     70) 
insert into tb values(1 ,       '2009-2' ,     70) 
insert into tb values(2 ,       '2009-2' ,     80) 
insert into tb values(1 ,       '2009-3' ,     90) 
go
select id,
  sum(case replace([month],'2009-','') when 1 then fen else 0 end)  [1],
  sum(case replace([month],'2009-','') when 2 then fen else 0 end)  [2],
  sum(case replace([month],'2009-','') when 3 then fen else 0 end)  [3],
  sum(case replace([month],'2009-','') when 4 then fen else 0 end)  [4],
  sum(case replace([month],'2009-','') when 5 then fen else 0 end)  [5],
  sum(case replace([month],'2009-','') when 6 then fen else 0 end)  [6],
  sum(case replace([month],'2009-','') when 7 then fen else 0 end)  [7],
  sum(case replace([month],'2009-','') when 8 then fen else 0 end)  [8],
  sum(case replace([month],'2009-','') when 9 then fen else 0 end)  [9],
  sum(case replace([month],'2009-','') when 10 then fen else 0 end) [10],
  sum(case replace([month],'2009-','') when 11 then fen else 0 end) [11],
  sum(case replace([month],'2009-','') when 12 then fen else 0 end) [12],
  avg(fen*1.0) [avg]
from tb
group by idid          1           2           3           4           5           6           7           8           9           10          11          12          avg
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
1           85          70          90          0           0           0           0           0           0           0           0           0           81.666666
2           70          80          0           0           0           0           0           0           0           0           0           0           75.000000(2 行受影响)

Answer 12

create table #tb(id int,[month] varchar(10),fen int)
insert into #tb values(1,'2009-1',85) 
insert into #tb values(2,'2009-1',70) 
insert into #tb values(1,'2009-2',70) 
insert into #tb values(2,'2009-2',80) 
insert into #tb values(1,'2009-3',90) 
go
select ID,
  sum(case when right([month],1)='1' then fen else 0 end) '一月',
 sum(case when right([month],1)='2' then fen else 0 end) '二月',
sum(case when right([month],1)='3' then fen else 0 end) '三月',
sum(case when right([month],1)='4' then fen else 0 end) '四月',
sum(case when right([month],1)='5' then fen else 0 end) '五月',
sum(case when right([month],1)='6' then fen else 0 end) '六月',
sum(case when right([month],1)='7' then fen else 0 end) '七月',
sum(case when right([month],1)='8' then fen else 0 end) '八月',
sum(case when right([month],1)='9' then fen else 0 end) '九月',
sum(case when right([month],1)='10' then fen else 0 end) '十月',
sum(case when right([month],1)='11' then fen else 0 end) '十一月',
sum(case when right([month],1)='12' then fen else 0 end) '十二月'
from #tb group by ID
ID          一月          二月          三月          四月          五月          六月          七月          八月          九月          十月          十一月         十二月
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           85          70          90          0           0           0           0           0           0           0           0           0
2           70          80          0           0           0           0           0           0           0           0           0           0(2 行受影响)

Answer 13

declare @sql varchar(8000)
set @sql='select id'
select @sql=@sql+',max(case month when  '''+month+''' then fen else 0 end) ['+month+']'
from (select distinct month from tb) a
set @sql=@sql+',avg(fen) [jine]  from tb group by id' 
exec(@sql)