Mysql行列轉換

Mysql行列轉換

1、創建表

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 |
| 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 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 |