这是完全可能的吗?我想要得到的东西是这样的:
+-------------------------------+
| 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
发布于 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 DESC
发布于 2018-07-09 02:02:20
我想您可以尝试一下,只需使用GROUP_CONCAT
一次,然后添加GROUP BY
SELECT CONCAT(data,'(',GROUP_CONCAT(DISTINCT another),');')
FROM T
group by data
https://stackoverflow.com/questions/51234627
复制相似问题