mysql - 可以改革列的表结构吗？

Question

我整天都在忙这个。我玩过很多 MySQL 代码，但我要么无能，要么不太擅长编码。

我有一张 table

name     id       type      amount
=================================
apple    21       cars      67
apple    21       bikes     85
apple    21       skates    557
apple    21       pajs      56
orange   34       bikes     345
orange   34       disks     678
orange   34       cars      234
orange   34       pajs      5678

我想编写一个查询，以这种形式返回表

name    id cars bikes skates pajas disks
=========================================
apple   21 67   85    557    56    0    
orange  34 234  345   0      5678  678

我真的不知道从哪里开始。抱歉，如果这是菜鸟问题，但 MySQL 有时真的很难概念化。

Answer 1

尝试使用PreparedStatement

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(ID),MAX(case when type = ''',
      type,
      ''' then amount ELSE NULL end) AS ',
       type
    )
  ) INTO @sql
FROM table1;

SET @sql = CONCAT('SELECT name, ', @sql, ' 
                   FROM table1 GROUP BY name');

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

SQLFiddle Demo

或者，

SELECT  name,
        MAX(ID) ID,
        MAX(case when type = 'cars' THEN amount ELSE NULL end) AS cars,
        MAX(case when type = 'bikes' THEN amount ELSE NULL end) AS bikes,
        MAX(case when type = 'skates' THEN amount ELSE NULL end) AS skates,
        MAX(case when type = 'pajas' THEN amount ELSE NULL end) AS pajas,
        MAX(case when type = 'disks' THEN amount ELSE NULL end) AS disks
FROM table1 
GROUP BY name;

SQLFiddle Demo