gpt4 book ai didi

mysql - 有没有更好的方法将 MySQL GROUP_CONCAT 输出拆分为多行?例如说每六 (6) 条记录后

转载 作者:行者123 更新时间:2023-11-30 21:28:38 25 4
gpt4 key购买 nike

我已经成功地实现了下面这个场景:

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 |
+-------------------------------------------------+

有什么建议吗?或者更好的方法来解决这个挑战?

最佳答案

您可以执行如下操作(支持 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

demo on 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

demo on dbfiddle.uk

关于mysql - 有没有更好的方法将 MySQL GROUP_CONCAT 输出拆分为多行?例如说每六 (6) 条记录后,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57482555/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com