Question

这个题的SQL语句怎么写？

表名为table的表内容如下
Year month value
2009 1 1.1
2009 2 1.2
2009 3 1.3
2009 4 1.4
2010 1 2.1
2010 2 2.2
2010 3 2.3
2010 4 2.4

要求查询结果为
year m1 m2 m3 m4
2009 1.1 1.2 1.3 1.4
2010 2.1 2.2 2.3 2.4
请写出SQL 语句。

还有，我想在两天之内了解SQL的主要用法，
对SQL的基本用法有一定了解，请高手
指点一下数据库的主要知识点！
谢谢了

Answer 1

交叉表，将行转换为列
select year，sum(case month=1 then value else 0 end) as m1,sum(case month=2 then value else 0 end) as m2,sum(case month=3 then value else 0 end) as m3,sum(case month=4 then value else 0 end) as m4
from table
group by year

Answer 2

declare @t table(year int,month int,value float)
insert into @t
select 2009,1,1.1 union
select 2009,2,1.2 union
select 2009,3,1.3 union
select 2009,4,1.4 union
select 2010,1,2.1 union
select 2010,2,2.2 union
select 2010,3,2.3 union
select 2010,4,2.4

select * from @t

--SQL--
select year ,
max(case month when 1 then value end) m1,
max(case month when 2 then value end) m2,
max(case month when 3 then value end) m3,
max(case month when 4 then value end) m4
from @t
group by year

--------------------------------------
(8 个资料列受到影响)
year month value
----------- ----------- ----------------------
2009 1 1.1
2009 2 1.2
2009 3 1.3
2009 4 1.4
2010 1 2.1
2010 2 2.2
2010 3 2.3
2010 4 2.4

(8 个资料列受到影响)

year m1 m2 m3 m4
---- ------ ------- -------- ---------
2009 1.1 1.2 1.3 1.4
2010 2.1 2.2 2.3 2.4
警告: 汇总或其他 SET 作业已删除 Null 值。

(2 个资料列受到影响)

Answer 3

想了解sql的话就去看他的帮助吧，sql server的帮助些的蛮经典的

Answer 4

用sql 2005提供的行列转置函数pivot完成

select [year],[1] as [M1], [2] AS [M2], [3] AS [M3], [4] AS [M4] FROM (select [year], [month],[value] from table)p pivot(sum([value]) for [month] in ([1],[2],[3],[4])) as pvt order by [year]

结果集如下

year M1 M2 M3 M4
----------- ---------------------- ---------------------- ---------------------- ----------------------
2009 1.1 1.2 1.3 1.4
2010 2.1 2.2 2.3 2.4

(2 个资料列受到影响)