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.

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

If I use the GROUP BY part I get this:

id uid

200 1

202 1

211 1

215 10 ...

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.

This is my desire Result:

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

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

Any pointers?

UPDATE

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;

解决方案

You want to use GROUP_CONCAT() like

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;