另一个GROUP_CONCAT中的MySQL GROUP_CONCAT
另一个GROUP_CONCAT中的MySQL GROUP_CONCAT
提问于 2018-07-09 01:50:03
这是完全可能的吗？我想要得到的东西是这样的：
+-------------------------------+
| data 1 (another1, another2); |
| data 2 (another1 ..... ) |
+-------------------------------+从表中可以看到类似这样的内容：
--------------------------------+
data1 | another1 | foreign key1 |
--------------------------------+
data1 | another2 | foreign key1 |
--------------------------------+
data2 | another1 | foreign key1 |
--------------------------------+我使用下面的代码
GROUP_CONCAT(DISTINCT CONCAT(data, ' (', another, ')') SEPARATOR ';\r\n ') as InOneCell并得到以下输出：
+-------------------------------+
| data 1 (another1); |
| data 1 (another2); |
| data 2 (another1); ... |
+-------------------------------+我试过了：
GROUP_CONCAT(DISTINCT CONCAT(data, ' (', GROUP_CONCAT(another SEPARATOR ', '), ')') SEPARATOR ';\r\n ') as InOneCell并得到错误“无效使用组函数”，这是真正的阴影-_-我做了组按前键由于复杂的查询(较低)。
我做错了什么，或者只在SQL上是不可能的？
下面是完整的查询：
SELECT
s.name,
s.surname,
gr.number AS 'group',
s.bitrh_date,
s.email,
s.registration_ip,
s.registration_time,
GROUP_CONCAT(DISTINCT CONCAT(dis.code, ' ', dis.title, ' (', GROUP_CONCAT(DISTINCT g.mark SEPARATOR ', '), ')') SEPARATOR ';\r\n ') as learning,
ROUND(AVG(g.mark), 2) AS average,
gr.semester,
ref.text AS reference
FROM t_students s
LEFT JOIN t_grades g
ON s.id = g.student
LEFT JOIN t_groups gr
ON s.group_id = gr.id
LEFT JOIN t_references ref
ON ref.author = s.id
LEFT JOIN t_program prog
ON gr.id = prog.group_id
LEFT JOIN t_disciplines dis
ON prog.discipline_id = dis.code
GROUP BY s.id
ORDER BY s.registration_time DESC回答 2
Stack Overflow用户
回答已采纳
发布于 2018-07-09 01:58:10
您应该使用group by (并且应该足够一个group_concat)
select CONCAT(data, ' (', GROUP_CONCAT(another SEPARATOR ', '), ');') as InOneCell
from my_table
group by data 或者，您可以使用子查询
select data, group_concat(my_group)
from (
select data, group_concat(another) my_group
group by data
) t
group by data 在你的例子中，你可以使用子查询，例如：
SELECT
s.name,
s.surname,
gr.number AS 'group',
s.bitrh_date,
s.email,
s.registration_ip,
s.registration_time,
GROUP_CONCAT(DISTINCT CONCAT(dis.code, ' ', dis.title, ' (', t.learning, ')') SEPARATOR ';\r\n ') as learning,
t.average,
gr.semester,
ref.text AS reference
FROM t_students s
LEFT JOIN (SELECT
student,
GROUP_CONCAT(DISTINCT mark SEPARATOR ', ') learning,
ROUND(AVG(mark), 2) AS average
from t_grades
group by student
) t on t.student = s.id
LEFT JOIN t_groups gr
ON s.group_id = gr.id
LEFT JOIN t_references ref
ON ref.author = s.id
LEFT JOIN t_program prog
ON gr.id = prog.group_id
LEFT JOIN t_disciplines dis
ON prog.discipline_id = dis.code
GROUP BY s.id
ORDER BY s.registration_time DESCStack Overflow用户
发布于 2018-07-09 02:02:20
我想您可以尝试一下，只需使用GROUP_CONCAT一次，然后添加GROUP BY
SELECT CONCAT(data,'(',GROUP_CONCAT(DISTINCT another),');')
FROM T
group by data页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51234627
复制相关文章
相似问题