Maison > base de données > tutoriel mysql > 按年月统计并行列转换(ms sqlserver2005)

按年月统计并行列转换(ms sqlserver2005)

WBOY
Libérer: 2016-06-07 15:45:49
original
1104 Les gens l'ont consulté

按年月统计并行列转换(ms sqlserver2005) 1 创建表 CREATE TABLE [dbo].[Orders]( [ID] [int] IDENTITY(1,1) NOT NULL, [Amount] [int] NULL, [Year] [int] NULL, [Month] [smallint] NULL, CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH

按年月统计并行列转换(ms sqlserver2005)

1 创建表

 

CREATE TABLE [dbo].[Orders](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Amount] [int] NULL,
 [Year] [int] NULL,
 [Month] [smallint] NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

2 初始化数据

insert into [Orders]
select 100,2010,1
union all
select 200,2010,2
union all
select 200,2010,2
union all
select 200,2010,2
union all
select 180,2010,5
union all
select 100,2010,7
union all
select 150,2010,8
union all
select 150,2010,8
union all
select 150,2010,8
union all
select 108,2010,10
union all
select 100,2010,11
union all
select 108,2010,12
union all
select 200,2009,2
union all
select 180,2009,5
union all
select 100,2009,7
union all
select 150,2009,8
union all
select 150,2009,8
union all
select 150,2009,8
union all
select 108,2009,10
union all
select 100,2009,11
union all
select 108,2009,12

select * from [Orders]

 

--------sql 2005------


SELECT *
FROM
( SELECT year,month,amount
FROM [Orders]) p
PIVOT
(SUM (amount)
FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS amount


-------sql 2000-----


select year,
[1] = Sum ( case when month ='1' then amount else 0 end ),
[2] = Sum ( case when month ='2' then amount else 0 end ),
[3] = Sum ( case when month ='3' then amount else 0 end ),
[4] = Sum ( case when month ='4' then amount else 0 end ),
[5] = Sum ( case when month ='5' then amount else 0 end ),
[6] = Sum ( case when month ='6' then amount else 0 end ),
[7] = Sum ( case when month ='7' then amount else 0 end ),
[8] = Sum ( case when month ='8' then amount else 0 end ),
[9] = Sum ( case when month ='9' then amount else 0 end ),
[10] = Sum ( case when month ='10' then amount else 0 end ),
[10] = Sum ( case when month ='10' then amount else 0 end ),
[12] = Sum ( case when month ='12' then amount else 0 end ),
[count] = Sum ( case when month '' then amount else 0 end )
from [Orders]
GROUP BY year

 

有更好的方法欢迎贴上来,学习下

Étiquettes associées:
source:php.cn
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Tutoriels populaires
Plus>
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal