获取列中的表行

您可以尝试以下查询：

SELECt idA  ,GROUP_CONCAt(CASE WHEN idB = 5 THEN val ELSE NULL END) AS `5`  ,GROUP_CONCAt(CASE WHEN idB = 6 THEN val ELSE NULL END) AS `6`  ,GROUP_CONCAt(CASE WHEN idB = 7 THEN val ELSE NULL END) AS `7`FROM MyTableGROUP BY idA

如果您不知道数量，则

idB

SET @sql = NULL;SELECt  GROUP_CONCAt(DISTINCT    CONCAt(      'GROUP_CONCAt(CASE WHEN `idB` = ''',      `idB`,      ''' THEN val ELSE NULL END) AS `',      `idB`, '`'    )  ) INTO @sqlFROM MyTable;SET @sql = CONCAt('SELECt idA, ', @sql,'          FROM MyTable         GROUP BY idA       ');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

输出：

| IDA |   5 |  6 |      7 |---------------------------|   1 |  50 |  0 | (null) ||   2 | 100 | 12 |      0 |

看到这个SQLFiddle