sql语句按年怎样分组 求大神支招

Question

比如下面：
比如字段两列
A B
2011 100
2011 60
2011 70
2012 200
2012 250
2013 300
2013 400

我想最后得到分组结果 比如：
2011年的为一个数组 array(
2011 => 100,
2011 => 60,
2011 => 70,

）
然后 2012和2013 和2011 一个道理
求大神支招

Answer 1

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table tb
insert [tb]
select 2011,100 union all
select 2011,60 union all
select 2011,70 union all
select 2012,200 union all
select 2012,250 union all
select 2013,300 union all
select 2013,400
--------------开始查询--------------------------
;with f as
(select id=row_number()over(order by getdate()),* from tb)

SELECT A.A,A.B,B.PX FROM F AS A
INNER JOIN
(
SELECT ROW_NUMBER()OVER( ORDER BY MIN(ID)) AS PX ,a FROM f GROUP BY a
) AS B
ON A.A=B.A
----------------结果----------------------------
/* A B PX

---

2011 100 1
2011 60 1
2011 70 1
2012 200 2
2012 250 2
2013 300 3
2013 400 3
*/