mysql列轉行
測試資料:
mysql>create table sale(month int(6),product varchar(32),amount decimal(20,2)
mysql> insert into sale values(201502,'apple',3500.00);
Query OK, 1 row affected (0.01 sec)
mysql> insert into sale values(201502,'banana',3000.00);
Query OK, 1 row affected (0.01 sec)
mysql> insert into sale values(201502,'pear',1000.00);
Query OK, 1 row affected (0.01 sec)
mysql> insert into sale values(201502,'orange',1000.00);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sale;
+--------+---------+---------+
| month | product | amount |
+--------+---------+---------+
| 201502 | apple | 3500.00 |
| 201502 | banana | 3000.00 |
| 201502 | pear | 1000.00 |
| 201502 | orange | 1000.00 |
+--------+---------+---------+
4 rows in set (0.00 sec)
現在前端展現要求需要以product為行的資料,具體實現如下:
SELECT
month,
max(case when product='apple' then mount ELSE 0 END) apple,
max(case when product='banana' then mount ELSE 0 END) banana,
max(case when product='pear' then mount ELSE 0 END) pear,
max(case when product='orange' then mount ELSE 0 END) orange
FROM sale GROUP BY month;
+--------+---------+---------+---------+---------+
| month | apple | banana | pear | orange |
+--------+---------+---------+---------+---------+
| 201502 | 3500.00 | 3000.00 | 1000.00 | 1000.00 |
+--------+---------+---------+---------+---------+
1 row in set (0.00 sec)
注:GROUP BY之後一定要用max函式,不然會出現banana,pear,orange會出現空
原因:
SELECT
month,
case when product='apple' then mount ELSE 0 END apple,
case when product='banana' then mount ELSE 0 END banana,
case when product='pear' then mount ELSE 0 END pear,
case when product='orange' then mount ELSE 0 END orange
FROM sale 結果是:
+--------+---------+---------+---------+---------+
| month | apple | banana | pear | orange |
+--------+---------+---------+---------+---------+
| 201502 | 3500.00 | 0 | 0 | 0 |
| 201502 | 0 | 3000.00 | 0 | 0 |
| 201502 | 0 | 0 | 1000.00 | 0 |
| 201502 | 0 | 0 | 0 | 1000.00 |
+--------+---------+---------+---------+---------+
4 rows in set (0.00 sec)
group by後只會取第一條記錄,除了apple其他欄位都是0,所以要加個max,把每個欄位的最大值取出來。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29989552/viewspace-2085266/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Mysql - 行轉列、列轉行MySql
- mysql 行轉列MySql
- mysql基礎 行轉列MySql
- MySQL 行轉列詳情鍾萼MySql
- MySQL列轉行巧錄資料MySql
- SQL 行轉列,列轉行SQL
- hive經典案列--top N(行轉列\列轉行)Hive
- oracle 11g的行轉列、列轉行Oracle
- sql的行轉列(PIVOT)與列轉行(UNPIVOT)SQL
- 玩轉Mysql命令列MySql命令列
- SQL列轉行SQL
- oracle 列轉行Oracle
- postgresql 列轉行SQL
- oracle列轉行Oracle
- Oracle行轉列、列轉行的Sql語句總結OracleSQL
- UltraEdit 列操作及列轉行
- mysql5中使用過程實現行轉列MySql
- oracle行列轉換-行轉列Oracle
- oracle行列轉換-列轉行Oracle
- 動態行轉列
- SQL 列行轉換SQL
- DB2行列轉置之行轉列DB2
- mysql~GROUP_CONCAT實現關係表的行轉列MySql
- 【MySQL】MySQL如何合併多行資料,行轉列,group_concat 多行合併MySql
- 寫了一個MySQL的行轉列的儲存過程薦MySql儲存過程
- oracle中多列轉行Oracle
- Hive行轉列函式Hive函式
- 動態固定行轉列
- 行轉列的應用
- Kettle實現行轉列
- Oracle行轉列函式Oracle函式
- sql實現行轉列SQL
- My SQL 列轉行操作SQL
- Databricks 第11篇:Spark SQL 查詢(行轉列、列轉行、Lateral View、排序)SparkSQLView排序
- mac中用命令列執行mysqlMac命令列MySql
- 行列轉換,列行轉換統計
- 老生常談SQL2005語句實現行轉列,列轉行SQL
- Oracle行轉列的函式Oracle函式