mysql行转列小技巧

-- 使用CASE语句
SELECT 
    student_id,
    MAX(CASE WHEN subject = 'Math' THEN score END) AS Math,
    MAX(CASE WHEN subject = 'Science' THEN score END) AS Science,
    MAX(CASE WHEN subject = 'English' THEN score END) AS English
FROM 
    student_scores
GROUP BY 
    student_id;

-- 使用PIVOT函数（适用于MySQL 8.0及以上版本）
SELECT 
    student_id,
    Math,
    Science,
    English
FROM 
    (SELECT student_id, subject, score FROM student_scores) src
PIVOT(
    MAX(score)
    FOR subject IN ('Math', 'Science', 'English')
) pvt;

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN subject = ''', subject, ''' THEN score END) AS ', subject))
INTO @sql
FROM student_scores;

SET @sql = CONCAT('SELECT student_id, ', @sql, ' FROM student_scores GROUP BY student_id');

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