CREATE TABLE `gas` (`id` INT(11) NOT NULL AUTO_INCREMENT,`oil` VARCHAR(100) NULL DEFAULT NULL,`district` VARCHAR(100) NULL DEFAULT NULL,`price` DOUBLE NULL DEFAULT '0',PRIMARY KEY (`id`))ENGINE=InnoDBAUTO_INCREMENT=11;
2、测试数据如下，select * from gas;
| id | oil | district | price || 1 | #0 | 宝安 | 11 |
| 2 | #0 | 福田 | 12 |
| 3 | #0 | 罗湖 | 13 |
| 4 | #93 | 宝安 | 21 |
| 5 | #93 | 福田 | 22 |
| 6 | #93 | 罗湖 | 23 |
| 7 | #97 | 宝安 | 31 |
| 8 | #97 | 福田 | 32 |
| 9 | #97 | 罗湖 | 33 |
3、行列转换：
select ifnull(oil,'total') as oil,sum(if(district='宝安',price,0)) AS BA,sum(if(district='福田',price,0)) AS FT,sum(if(district='罗湖',price,0)) AS LH,SUM(price) AS TOTALfrom gasgroup by oil with rollup ;
执行结果如下：
| oil | BA | FT | LH | TOTAL |
| #0 | 11 | 12 | 13 | 36 |
| #93 | 21 | 22 | 23 | 66 |
| #97 | 31 | 32 | 33 | 96 |
| total | 63 | 66 | 69 | 198 |