我怎样才能通过mysql仅在一行中加入多行?

范例:

学生桌

Sno.| Name  |  Subjects
1.  | ABC   |  English
2.  | ABC   |  Mathematics
3.  | ABC   |  Science
4.  | FMC |  French
5.  | ABC   |  Russian
6.  | JBC   |  French

现在我想要这种格式
Sno.| Name |   Sub1 |  Sub2 | Sub3 |  Sub4 |
1.  | ABC |   Eng  |  Maths| Science| Russian
2.  | FMC |    French| Null| Null   | Null
3.  | JBC |   French| Null | Null   | Null

我不确定该怎么做?我应该创建视图还是表格?

我想一个观点会很好。

最佳答案

我同意其他答案,即GROUP_CONCAT和PHP拆分逗号分隔的值可能是最好的方法,但是,如果由于任何其他原因需要通过Pure SQL建议的输出,则建议使用以下方法之一。

1. Self Joins

SELECT  t1.Name,
        MIN(t1.Subject) AS Sub1,
        MIN(t2.Subject) AS Sub2,
        MIN(t3.Subject) AS Sub3,
        MIN(t4.Subject) AS Sub4
FROM    Students t1
        LEFT JOIN Students T2
            ON t1.Name = t2.Name
            AND t2.Subject > t1.Subject
        LEFT JOIN Students T3
            ON t2.Name = t3.Name
            AND t3.Subject > t2.Subject
        LEFT JOIN Students T4
            ON t3.Name = t4.Name
            AND t4.Subject > t3.Subject
GROUP BY t1.Name;

2. Using a ROW_NUMBER Type function to aggregate
SELECT   Name,
         MAX(IF(RowNum = 1,Subject, NULL)) AS Sub1,
         MAX(IF(RowNum = 2,Subject, NULL)) AS Sub2,
         MAX(IF(RowNum = 3,Subject, NULL)) AS Sub3,
         MAX(IF(RowNum = 4,Subject, NULL)) AS Sub4
FROM     (    SELECT   Name,
                       Subject,
                       @r:= IF(@Name = Name, @r + 1, 1) AS RowNum,
                       @Name:= Name AS Name2
              FROM    Students,
                      (SELECT @Name:='') n,
                      (SELECT @r:= 0) r
              ORDER BY Name, Sno
          ) t
GROUP BY Name

10-08 11:06