MYSQL数组聚合函数，例如PostgreSQL array_agg [英] MYSQL array aggregate function like PostgreSQL array_agg

问题描述

我在MySQL上有两个表，我想知道在MySQL上是否有任何聚合函数，例如postgreSQL的array_agg().

I got two tables on MYSQL, I wonder if there is any aggregate function on MYSQL as array_agg() FROM postgreSQL.

表1属性只有8条记录 表2记录了谁捕获了该属性，因此对于同一属性，有时可能是1或n次，我得到了这个Qry:

TABLE 1 properties Only have 8 records TABLE 2 records who captured the property, so sometimes can be 1 or n times for the same property, and I got this Qry:

SELECT p.id, pcb.users_admin_id as uid
FROM properties p
INNER JOIN prop_captured_by pcb ON p.id = pcb.property_id
-- GROUP BY p.id

id    uid
200   1
200   80
202   1
202   80
211   1
211   10
211   81
215   10 ...

如果我使用GROUP BY部分，我会得到:

If I use the GROUP BY part I get this:

id    uid
200   1
202   1
211   1
215   10 ...

除了users_admin_id的第一个值之外，其他所有数据都丢失了.我知道我可以用postgreSQL的array_agg()函数达到我想要的结果，但是我不知道如何在MYSQL上做到这一点.

Losing any other data than the first value for users_admin_id. I know that I can achieve my desired result with array_agg() function from postgreSQL but I can't figured out how to do it on MYSQL.

这是我的愿望结果:

id    uid
200   1,80 //an array, I don't mind about the separator, could be anything.
202   1,80
211   1,10,81
215   10 ...

我尝试了UNION，GROUP BY，INNER JOIN ...不走运... 有指针吗?

I have tried UNION, GROUP BY, INNER JOIN... no luck... Any pointers?

更新

我正在从中使用许多关系家伙希望对其他人有用.我需要从第三张表中添加用户名，所以最终查询如下:

I am using the many to many relations from this guy. Hopes it is useful for someone else. I needed to add the user's name from a third table, so the final query looks like this:

SELECT p.id, group_concat(pcb.users_admin_id) as uid, group_concat(ua.name) as uin
FROM properties p
INNER JOIN prop_captured_by pcb ON p.id = pcb.property_id
INNER JOIN users_admin ua ON ua.id = pcb.users_admin_id
group by p.id;

推荐答案

您要使用GROUP_CONCAT()之类的

SELECT p.id, group_concat(pcb.users_admin_id) as uid
FROM properties p
INNER JOIN prop_captured_by pcb 
ON p.id = pcb.property_id
group by p.id;

这篇关于MYSQL数组聚合函数，例如PostgreSQL array_agg的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！