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=InnoDB AUTO_INCREMENT=11;
2、測試數據如下,select * from gas;
| id | oil | district | price || 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 TOTAL from gas group 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 |