Есть ли лучший способ разделить вывод MySQL GROUP_CONCAT на несколько строк? например, скажите после каждых шести (6) записей

Question

Я успешно достиг этого сценария ниже:

SELECT GROUP_CONCAT(coursecode) 
    FROM (
    SELECT coursecode FROM Table18
    WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39

    UNION  

    SELECT coursecode FROM Table17
    WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39
) s

WHERE coursecode 
NOT IN (
    SELECT coursecode 
    FROM Table18
    WHERE regno = 'StudentXYZ' 
    AND (ca_score + exam_score) >= 40

    UNION

    SELECT coursecode 
    FROM Table17
    WHERE regno = 'StudentXYZ' 
    AND (ca_score + exam_score) >= 40
    )

Это прекрасно работает! Вот пример результата ниже:

+----------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(coursecode)                                                                                 |
+----------------------------------------------------------------------------------------------------------+
| EDU222,EDU497,POS302,POS405,POS420,EDU224,EDU311,EDU312,EDU313,GST304,GST305,POS304,POS305,POS308,POS309 |
+----------------------------------------------------------------------------------------------------------+

Но вот чего я хочу добиться:

+-------------------------------------------------+
| GROUP_CONCAT(coursecode)                        |
+-------------------------------------------------+
| EDU222, EDU497, POS302, POS405, POS420, EDU224, |
| EDU311, EDU312, EDU313, GST304, GST305, POS304, |
| POS305, POS308, POS309                          |
+-------------------------------------------------+

Какие-либо предложения? или лучший способ решить эту проблему?

Answer 1

Вы можете сделать что-то вроде следующего (поддержка MySQL 5.7 / MariaDB <10.2):

SELECT GROUP_CONCAT(coursecode)
FROM (
    SELECT coursecode, @gn:=@gn+1, CEIL(@gn / 6) gn
    FROM (
        SELECT coursecode FROM Table18
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39

        UNION  

        SELECT coursecode FROM Table17
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39
    ) s, (SELECT @gn:=0)gn
    WHERE coursecode NOT IN (
        SELECT coursecode 
        FROM Table18
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) >= 40

        UNION

        SELECT coursecode 
        FROM Table17
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) >= 40
    )
) group_view GROUP BY group_view.gn

демо на dbfiddle.uk

Начиная с MySQL 8.0 / MariaDB 10.2 вы можете использовать ту же логику, но с ROW_NUMBER вместо переменной. Таким образом, ваш запрос может выглядеть так:

SELECT GROUP_CONCAT(coursecode)
FROM (
    SELECT coursecode, CEIL(ROW_NUMBER() OVER (ORDER BY code ASC) / 6) gn
    FROM (
        SELECT coursecode FROM Table18
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39

        UNION  

        SELECT coursecode FROM Table17
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39
    ) s
    WHERE coursecode NOT IN (
        SELECT coursecode 
        FROM Table18
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) >= 40

        UNION

        SELECT coursecode 
        FROM Table17
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) >= 40
    )
) group_view GROUP BY group_view.gn

демо на dbfiddle.uk