使用CREATE VIEW将列值拆分为多个列

您可以简单地使用

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_shippingFROM Table1 GROUP BY id;

看到这个SQLFiddle

如果您的 班级 数目未知，请尝试此动态查询

SET @sql = NULL;SELECt  GROUP_CONCAt(DISTINCT    CONCAt(      'SUM(case when class = ''',      class,      ''' then value else 0 end) AS `value_',      class, '`'    )  ) INTO @sqlFROM 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 ║╚════╩═════════════╩════════════════╝

看到这个SQLFiddle