50分求一条SQL语句

Question

有这么一个表和数据。
id   year   q   season
1    2000   11     1
2    2000   11     2
3    2000   12     3
4    2000   12     4
5    2001   12     1
6    2001   12     2
7    2001   13     3
8    2001   13     4
现在要查出如下格式的数据：
year season1 season2 season3 season4
2000    11     11       12      12
2001    12     12       13      13
请问各位该如何编写此条SQL语句呀才能查询出来呀？只能一条SQL语句哦

Answer 1

有关行转列的几种做法
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?24004

Answer 2

select `year`,
sum(if(season=1,q,0)) as season1,
sum(if(season=2,q,0)) as season2,
sum(if(season=3,q,0)) as season3,
sum(if(season=4,q,0)) as season4
from tb_name
group by `year`

Answer 3

mysql> select * from t_fuyunyouzi1982;
+------+------+------+--------+
| id   | year | q    | season |
+------+------+------+--------+
|    1 | 2000 |   11 |      1 |
|    2 | 2000 |   11 |      2 |
|    3 | 2000 |   12 |      3 |
|    4 | 2000 |   12 |      4 |
|    5 | 2001 |   12 |      1 |
|    6 | 2001 |   12 |      2 |
|    7 | 2001 |   13 |      3 |
|    8 | 2001 |   13 |      4 |
+------+------+------+--------+
8 rows in set (0.00 sec)mysql> select year,
    ->  sum(if(season=1,q,0)) as season1,
    ->  sum(if(season=2,q,0)) as season2,
    ->  sum(if(season=3,q,0)) as season3,
    ->  sum(if(season=4,q,0)) as season4
    -> from t_fuyunyouzi1982
    -> group by year;
+------+---------+---------+---------+---------+
| year | season1 | season2 | season3 | season4 |
+------+---------+---------+---------+---------+
| 2000 |      11 |      11 |      12 |      12 |
| 2001 |      12 |      12 |      13 |      13 |
+------+---------+---------+---------+---------+
2 rows in set (0.08 sec)mysql>