水平显示MySQL记录

Question

通常，我们从MySQL表中获取数据，并逐行显示获取的记录。但有时我们需要水平地显示记录如下。

Name * Student-ID * subject-1 * subject-2 * subject-3 * subject-4 * subject-5 ... So On
John      1            20           50          34          50         45 
Mercy     3            40           20          82          23         10
James     8            30           60          40          50         30
Agi       19           50           10          23          45         30

现在我正在寻找一种能够产生上述结果的方法。下面是我想给出的MySQL表，上面列出了所需的结果.

表 subjects 按类存储主题如下

1. 主语
2. 主体名称
3. 检验

下列考试资料表

1. assessment_id
2. 田园

表examinations存储学生成绩和相关信息如下

1. 学生d
2. fname
3. 国名
4. id_subject
5. 得分

- examinationid == assessment\_id 
- id\_subject AND idsubject == subjectid

​

向参加考试的班级的所有学生询问身份证考试(1)

SELECT *
FROM examinations AS exam
INNER JOIN examinfo AS info ON info.idsubject = exam.id_subject
WHERE info.assessment_id = 1

上面的查询将产生垂直的结果，因此如何水平地转换或查询表。我错过了它，因为我以前从未以这种方式使用过SQL。谢谢你的手

Answer 1

您正在寻找MYSQL支点。

静态方式：使用带组的条件用例

SELECT CONCAT(exam.fname," ",exam.lname) AS Name, exam.studentid, 
 SUM(CASE WHEN exam.id_subject = 1 THEN exam.score ELSE "NA" END) AS subject1,
 SUM(CASE WHEN exam.id_subject = 2 THEN exam.score ELSE "NA" END) AS subject2,  
 SUM(CASE WHEN exam.id_subject = 3 THEN exam.score ELSE "NA" END) AS subject3,
 SUM(CASE WHEN exam.id_subject = 4 THEN exam.score ELSE "NA" END) AS subject4,  
 SUM(CASE WHEN exam.id_subject = 5 THEN exam.score ELSE "NA" END) AS subject5
FROM examinations AS exam
INNER JOIN examinfo AS info ON info.idsubject = exam.id_subject
WHERE info.assessment_id = 1
GROUP BY exam.studentid

动态方式：将塞纳特与塞纳特结合使用

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT 
    CONCAT('SUM(CASE WHEN id_subject= "', 
    id_subject, '" THEN score ELSE "NA" END) AS '
    , 'Subject-', id_subject))
INTO @sql
FROM
  examinations;

SET @sql = CONCAT('SELECT CONCAT(fname," ",lname) AS Name, 
                   studentid, ', @sql, ' 
                  FROM examinations
                  INNER JOIN examinfo AS info ON info.idsubject = id_subject
                  WHERE info.assessment_id = 1
                  GROUP BY studentid');

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

Answer 2

使用下面的查询

SELECT CONCAT(exam.fname," ",exam.lname) AS Name, exam.studentid, 
 (CASE WHEN exam.subjectid = 1 THEN exam.score END ),
 (CASE WHEN exam.subjectid = 2 THEN exam.score END ),  
 (CASE WHEN exam.subjectid = 3 THEN exam.score END ),
 (CASE WHEN exam.subjectid = 4 THEN exam.score END ),  
 (CASE WHEN exam.subjectid = 5 THEN exam.score END ),
FROM examinations AS exam
INNER JOIN examinfo AS info ON info.idsubject = exam.id_subject
WHERE info.assessment_id = 1
GROUP BY exam.studentid