使用CREATE VIEW将列值拆分为多个列 [英] Split column values into multiple columns with CREATE VIEW

问题描述

通过MySQL查询，我如何像示例A那样取一个表:

With a MySQL query, how can I take a table like in Example A:

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

并创建一个类似于示例B的视图吗?

And create a view like Example B?

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

推荐答案

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

You can simply use SUM() function for that:

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;

请参见此SQLFiddle

如果您不知道 class 的数量，请尝试此动态查询

See this SQLFiddle

If you have unknown number of class then try this Dynamic query

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 ║
╚════╩═════════════╩════════════════╝

这篇关于使用CREATE VIEW将列值拆分为多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！