在mysql中将表行变成列 [英] turning table rows into columns in mysql

问题描述

说我有一个类似于以下内容的数据库:

Say I have a database similar to the following:

表student_info:

Table student_info:

id    name
111   jon
112   dan
113   david
...

和表分数:

item_id    student_id    score
01         111           37
02         111           45
01         112           55
02         112           44
01         113           66
02         113           45
...

是否可以通过mysql查询生成下表?

Is it possible to do a mysql query to generate the following table?:

Student_Name  ITEM_1_SCORE  ITEM_2_SCORE
jon           37            45
dan           55            44
david         66            45
...

如果是这样，语法是什么?我不知道这是联接操作还是其他操作?

If so, what would the syntax be? I don't know if this is a join operation or something else?

谢谢.

推荐答案

如果item_id只有两个值，那么硬编码值就可以了.例子

If you have only two values for item_id, then it is fine to hard code values. Example

SELECT  a.Name AS Student_Name,
        MAX(CASE WHEN item_id = '01' THEN b.score END) Item_1_Score,
        MAX(CASE WHEN item_id = '02' THEN b.score END) Item_2_Score
FROM    student_info a
        LEFT JOIN scores b
            ON a.id = b.student_ID
GROUP   BY a.Name

• SQLFiddle演示
• SQLFiddle Demo

否则，当您不知道分数的数量时，Dynamic SQL是更可取的.

Otherwise, when you have unknow number of scores, a Dynamic SQL is much prefered.

SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN item_id = ''',
               item_id,
               ''' THEN Score END) AS ',
               CONCAT('`Item_', item_id, '_Score`')
               )) INTO @sql
FROM scores;

SET @sql = CONCAT('SELECT   a.Name AS Student_Name, ', @sql, ' 
                    FROM    student_info a
                            LEFT JOIN scores b
                                ON a.id = b.student_ID
                    GROUP   BY a.Name');

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

• SQLFiddle演示
• SQLFiddle演示(带有附加item_id 的)
• SQLFiddle Demo
• SQLFiddle Demo (with extra item_id)

两个查询的输出都相同

╔══════════════╦══════════════╦══════════════╗
║ STUDENT_NAME ║ ITEM_1_SCORE ║ ITEM_2_SCORE ║
╠══════════════╬══════════════╬══════════════╣
║ dan          ║           55 ║           44 ║
║ david        ║           66 ║           45 ║
║ jon          ║           37 ║           45 ║
╚══════════════╩══════════════╩══════════════╝

这篇关于在mysql中将表行变成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！