Mysql:Wie kann man mehr als eine Zeile in eine einzige Zeile mit unterschiedlichen Feldnamen verketten?

Question

id  Subject   mark   Year
-------------------------
1   Maths      32    2008
1   Science    40    2009
1   Science    45    2008
1   English    50    2009
1   English    60    2008

Ich suche nach einem Ergebnis wie diesem:

id  Maths   Science   English
-----------------------------
1   32      40 & 45   50 & 60

Bitte beraten Sie mich. Ich verwende MySQL.

Answer 1

Als @Mark schlug vor , GROUP_CONCAT() kann das folgende Ergebnis liefern:

SELECT   id, subject, GROUP_CONCAT(mark SEPARATOR ' & ') marks
FROM     results 
GROUP BY id, subject;
+------+---------+---------+
| id   | subject | marks   |
+------+---------+---------+
|    1 | English | 50 & 60 |
|    1 | Maths   | 32      |
|    1 | Science | 40 & 45 |
+------+---------+---------+
3 rows in set (0.00 sec)

Aus diesem Testfall:

CREATE TABLE results (id int, subject varchar(10), mark int);

INSERT INTO results VALUES (1, 'Maths', 32);
INSERT INTO results VALUES (1, 'Science', 40);
INSERT INTO results VALUES (1, 'Science', 45);
INSERT INTO results VALUES (1, 'English', 50);
INSERT INTO results VALUES (1, 'English', 60);

Eine andere Möglichkeit, das Problem zu lösen, wäre die Verwendung einer Unterabfrage für jedes Thema:

SELECT    r.id,
          (SELECT   GROUP_CONCAT(r_eng.mark SEPARATOR ' & ')
           FROM     results r_eng
           WHERE    r_eng.subject = 'English' AND r_eng.id = r.id) English,
          (SELECT   GROUP_CONCAT(r_eng.mark SEPARATOR ' & ') 
           FROM     results r_eng
           WHERE    r_eng.subject = 'Maths' AND r_eng.id = r.id) Maths,
          (SELECT   GROUP_CONCAT(r_eng.mark SEPARATOR ' & ') 
           FROM     results r_eng
           WHERE    r_eng.subject = 'Science' AND r_eng.id = r.id) Science
FROM      results r
GROUP BY  r.id;

Dies führt zu folgendem Ergebnis:

+------+---------+-------+---------+
| id   | English | Maths | Science |
+------+---------+-------+---------+
|    1 | 50 & 60 | 32    | 40 & 45 |
+------+---------+-------+---------+
1 row in set (0.01 sec)

---

UPDATE:

Nach den Kommentaren sieht es so aus, als müssten Sie die year Bereich in Betracht. Glücklicherweise ist die GROUP_CONCAT() Funktion nimmt eine ORDER BY Klausel, die wir verwenden können. Beginnen wir mit einem neuen Testfall mit dem Feld Jahr:

CREATE TABLE results (id int, subject varchar(10), mark int, year int);

INSERT INTO results VALUES (1, 'Maths', 32, 2008);
INSERT INTO results VALUES (1, 'Science', 40, 2009);
INSERT INTO results VALUES (1, 'Science', 45, 2008);
INSERT INTO results VALUES (1, 'English', 50, 2009);
INSERT INTO results VALUES (1, 'English', 60, 2008);

SELECT * FROM results;
+------+---------+------+------+
| id   | subject | mark | year |
+------+---------+------+------+
|    1 | Maths   |   32 | 2008 |
|    1 | Science |   40 | 2009 |
|    1 | Science |   45 | 2008 |
|    1 | English |   50 | 2009 |
|    1 | English |   60 | 2008 |
+------+---------+------+------+
5 rows in set (0.00 sec)

Dann könnten wir die GROUP_CONCAT() Funktion mit der ORDER BY Klausel wie folgt:

SELECT   id, 
         subject, 
         GROUP_CONCAT(mark ORDER BY year SEPARATOR ' & ') marks, 
         GROUP_CONCAT(year ORDER BY year SEPARATOR ' & ') years
FROM     results 
GROUP BY id, subject;

+------+---------+---------+-------------+
| id   | subject | marks   | years       |
+------+---------+---------+-------------+
|    1 | English | 60 & 50 | 2008 & 2009 |
|    1 | Maths   | 32      | 2008        |
|    1 | Science | 45 & 40 | 2008 & 2009 |
+------+---------+---------+-------------+
3 rows in set (0.00 sec)

Schließlich, um GROUP BY alles in einer horizontalen Zeile zu finden, können wir die Technik der Unterabfrage anwenden, die wir im vorherigen Beispiel verwendet haben:

SELECT    r.id,
          (SELECT  GROUP_CONCAT(r_eng.mark ORDER BY year SEPARATOR ' & ') 
           FROM    results r_eng
           WHERE   r_eng.subject = 'English' AND r_eng.id = r.id) English,
          (SELECT  GROUP_CONCAT(r_eng.year ORDER BY year SEPARATOR ' & ')
            FROM   results r_eng
            WHERE  r_eng.subject = 'English' AND r_eng.id = r.id) Years_English,
          (SELECT  GROUP_CONCAT(r_eng.mark ORDER BY year SEPARATOR ' & ')
           FROM    results r_eng
           WHERE   r_eng.subject = 'Maths' AND r_eng.id = r.id) Maths,
          (SELECT  GROUP_CONCAT(r_eng.year ORDER BY year SEPARATOR ' & ')
            FROM   results r_eng
            WHERE  r_eng.subject = 'Maths' AND r_eng.id = r.id) Years_Maths,
          (SELECT  GROUP_CONCAT(r_eng.mark ORDER BY year SEPARATOR ' & ')
           FROM    results r_eng
           WHERE   r_eng.subject = 'Science' AND r_eng.id = r.id) Science,
          (SELECT  GROUP_CONCAT(r_eng.year ORDER BY year SEPARATOR ' & ')
            FROM   results r_eng
            WHERE  r_eng.subject = 'Science' AND r_eng.id = r.id) Years_Science
FROM      results r
GROUP BY  r.id;

Das Ergebnis ist das Folgende:

+----+---------+---------------+-------+-------------+---------+---------------+
| id | English | Years_English | Maths | Years_Maths | Science | Years_Science |
+----+---------+---------------+-------+-------------+---------+---------------+
|  1 | 60 & 50 | 2008 & 2009   | 32    | 2008        | 45 & 40 | 2008 & 2009   |
+----+---------+---------------+-------+-------------+---------+---------------+
1 row in set (0.01 sec)

Wenn Sie die Marken und die Jahre in absteigender Reihenfolge sortiert haben möchten, können Sie einfach die DESC Schlüsselwort nach jeder ORDER BY year .

Answer 2

Für den ersten Schritt sehen Sie sich bitte die group_concat() functiopn in mysql, es könnte hilfreich sein

SELECT `id`,
       `subject`,
       group_concat( `mark`
                     ORDER BY `year` DESC
                     SEPARATOR ' & '
                   ) marks
  FROM `subjects` 
 GROUP BY `id`,
          `subject`

UPDATE Ich habe es geschafft, bis nach

SELECT `id`,
       IF (`subject` = 'English', `marks`, NULL) AS English,
       IF (`subject` = 'Maths', `marks`, NULL) AS Maths,
       IF (`subject` = 'Science', `marks`, NULL) AS Science
  FROM ( SELECT `id`, 
                `subject`, 
                group_concat( `mark` 
                              ORDER BY `year` DESC 
                              SEPARATOR ' & ' 
                            ) marks 
           FROM `subjects`  
          GROUP BY `id`, 
                   `subject` 
         ) x
GROUP BY `id`,
         `subject` 

die zurückgibt

+----+---------+-------+---------+
| id | English | Maths | Science |
+----+---------+-------+---------+
|  1 | 50 & 60 | NULL  | NULL    |
|  1 | NULL    | 32    | NULL    |
|  1 | NULL    | NULL  | 40 & 45 |
+----+---------+-------+---------+
3 rows in set (0.01 sec)

aber ich kann die letzte Gruppierung nicht so hinbekommen, dass alles in einer einzigen Zeile erscheint. Ich weiß nicht, ob jemand anderes mir helfen kann, den letzten Schritt richtig zu machen, wenn ich diesen Ansatz für das Problem nehme.... Normalerweise würde ich das Ergebnis an PHP zurückgeben "wie es ist", und die letzte Gruppierung dort tun; aber eine vollständige MySQL-Lösung wäre interessant.