今天上午在一个JAVA群里,有人问了一个关于sql的问题,他用的应该是oracle或者DB2之类的数据库,偶也不清楚,就变变数据类型,用mysql做例子吧。大概也就是这个意思,要把如下的一个形式的表数据,

mysql> select * from table_sql1;
+--------+---------+-------+
| myyear | mymonth | amout |
+--------+---------+-------+
|   1991 |       1 |   1.1 |
|   1991 |       2 |   1.2 |
|   1991 |       3 |   1.3 |
|   1991 |       4 |   1.4 |
|   1992 |       1 |   2.1 |
|   1992 |       2 |   2.2 |
|   1992 |       3 |   2.3 |
|   1992 |       4 |   2.4 |
+--------+---------+-------+

通过一条SQL,查询出需要的结果,需要的结果是这个样子:

+--------+------+------+------+------+
| myyear | m1   | m2   | m3   | m4   |
+--------+------+------+------+------+
|   1991 |  1.1 |  1.2 |  1.3 |  1.4 |
|   1992 |  2.1 |  2.2 |  2.3 |  2.4 |
+--------+------+------+------+------+

先附上生成测试数据的SQL语句:

create table table_sql1(
myyear int(4),
mymonth  int(1),
amout   double
);

insert into table_sql1 values(1991,1,1.1);
insert into table_sql1 values(1991,2,1.2);
insert into table_sql1 values(1991,3,1.3);
insert into table_sql1 values(1991,4,1.4);
insert into table_sql1 values(1992,1,2.1);
insert into table_sql1 values(1992,2,2.2);
insert into table_sql1 values(1992,3,2.3);
insert into table_sql1 values(1992,4,2.4);

具体思路,,自己看看实现语句就OK了。

select  myyear,
        Sum(Case mymonth When '1' then amout else 0 end) As m1,
        Sum(Case mymonth When '2' then amout else 0 end) As m2,
        Sum(Case mymonth When '3' then amout else 0 end) As m3,
        Sum(Case mymonth When '4' then amout else 0 end) As m4
from table_sql1 group by myyear;