依据上面的表信息输出以下的结果:
以下是建库和表结构据:
create table DeptSales ( deptID int, SubjMonth int , sales int , deptname varchar(50) )
insert into deptsales (deptid ,subjmonth,sales) values (1,1,55); insert into deptsales (deptid ,subjmonth,sales) values (2,1,66); insert into deptsales (deptid ,subjmonth,sales) values (3,1,77); insert into deptsales (deptid ,subjmonth,sales) values (2,2,34); insert into deptsales (deptid ,subjmonth,sales) values (4,2,56); insert into deptsales (deptid ,subjmonth,sales) values (3,3,78);
运行sql语句:
mysql> select deptID as '部门',sum(case SubjMonth when 1 then sales end) '一月销售额',sum(case SubjMonth when 2 then sales end) '二月销售额',sum(case SubjMonth when 3 then sales end) '三月销售额',sum(case SubjMonth when 4 then sales end) '四月销售额' from deptsales d group by deptID; +------+------------+------------+------------+------------+ | 部门 | 一月销售额 | 二月销售额 | 三月销售额 | 四月销售额 | +------+------------+------------+------------+------------+ | 1 | 55 | NULL | NULL | NULL | | 2 | 66 | 34 | NULL | NULL | | 3 | 77 | NULL | 78 | NULL | | 4 | NULL | 56 | NULL | NULL | +------+------------+------------+------------+------------+ 4 rows in set