mysql - 使用 CREATE VIEW 将列值拆分为多列

Question

使用 MySQL 查询，我如何获取示例 A 中的表:

Example A
+------+---------------+----------+
| id   | value         | class    |
+------+---------------+----------+
| 1    | 33.00         | total    |
| 1    | 12.00         | shipping |
| 2    | 45.00         | total    |
| 2    | 15.00         | shipping |
+------+---------------+----------+

然后创建一个类似示例 B 的 View ？

Example B
+------+---------------+---------------+
| id   | value_total   | value_shipping|
+------+---------------+---------------+
| 1    | 33.00         | 12.00         |
| 2    | 45.00         | 15.00         |
+------+---------------+---------------+

Answer 1

您可以简单地使用 SUM() 函数:

SELECT id
,SUM(CASE WHEN class = 'total' THEN value ELSE 0 END) AS value_total
,SUM(CASE WHEN class = 'shipping' THEN value ELSE 0 END) AS value_shipping
FROM Table1 
GROUP BY id;

参见this SQLFiddle

如果您有未知数量的类，那么试试这个动态查询

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when class = ''',
      class,
      ''' then value else 0 end) AS `value_',
      class, '`'
    )
  ) INTO @sql
FROM Table1;


SET @sql = CONCAT('SELECT id, ', @sql, '
                  FROM Table1 
                  GROUP BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

输出:

╔════╦═════════════╦════════════════╗
║ ID ║ VALUE_TOTAL ║ VALUE_SHIPPING ║
╠════╬═════════════╬════════════════╣
║  1 ║          33 ║             12 ║
║  2 ║          45 ║             15 ║
╚════╩═════════════╩════════════════╝

参见this SQLFiddle