您可以使用GROUP BY和MAX模拟数据透视。MySQL也支持IF声明。SELECT ID,
MAX(IF(`order` = 1, data, NULL)) data1,
MAX(IF(`order` = 2, data, NULL)) data2FROM TableAGROUP BY ID
如果您有多个值order,则动态SQL可能更合适,因此您不必修改查询:SET @sql = NULL;SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(`order` = ', `order`, ',data,NULL)) AS data', `order`)
) INTO @sqlFROM TableName;SET @sql = CONCAT('SELECT ID, ', @sql, '
FROM TableName
GROUP BY ID');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
两种查询的输出:
╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║ 1 ║ P ║ S ║
║ 2 ║ R ║ Q ║
╚════╩═══════╩═══════╝