MySQL JOIN/GROUP_CONCAT第二张表? [英] MySQL JOIN / GROUP_CONCAT second table?

问题描述

所以我有这个查询可以很好地工作:

So I have this query that works perfectly:

SELECT users.*,
GROUP_CONCAT(categories.category_name) AS categories
FROM users
LEFT OUTER JOIN user_categories ON users.user_id = user_categories.user_id
LEFT OUTER JOIN categories ON user_categories.category_id = categories.category_id
WHERE users.user_city = 'brooklyn'
GROUP BY users.user_id
LIMIT 10;

说我有另一个保存电话号码的表，对于用户"来说，用户可以有任意数量的电话号码...我该如何处理与类别完全相同的事情?换句话说，我想获得另一列，其中包含在电话"表中找到的所有具有相同"user_id"的phone_numbers，并将它们连接在一起(phone1，phone2，phone3)?我已经尝试过:

Say I have another table that holds phone numbers, for the "users" a user can have any number of phone numbers... How would I go about doing round about the exact same thing I am doing wit the categories? In other words, I would like to get another column with ALL of the phone_numbers found in the "phones" table that have the same "user_id" and concat them together(phone1, phone2, phone3)? I have tried:

SELECT users.*,
GROUP_CONCAT(phones.phone_number) AS phone_numbers,
GROUP_CONCAT(categories.category_name) AS categories
FROM users
LEFT OUTER JOIN phones ON users.user_id = phones.user_id
LEFT OUTER JOIN user_categories ON users.user_id = user_categories.user_id
LEFT OUTER JOIN categories ON user_categories.category_id = categories.category_id
WHERE users.user_city = 'brooklyn'
GROUP BY users.user_id
LIMIT 10;

没有运气...或者至少执行了查询，但是它做了一些奇怪的重复操作...任何帮助都会很棒！

With no luck... or at least the query executes but it does some weird duplication thing... any help would be awesome!

谢谢！

推荐答案

它确实很奇怪，因为存在某些行的叉积.您可以使用DISTINCT关键字仅获取唯一的电话号码:

It does weird things, becaue there is a cross product of certain rows. You can use the DISTINCT keyword to get only unique phone numbers:

GROUP_CONCAT(DISTINCT phones.phone_number) AS phone_numbers,

检查文档.或者，您可以在另一个查询中获取电话号码，在该查询中，您仅选择条件为WHERE phones.user_id IN (x, x, x, ...)(x是从第一个查询返回的ID)的电话号码.

Check the documentation. Alternatively, you can get the phone numbers in another query where you would select only the phone numbers with a condition like WHERE phones.user_id IN (x, x, x, ...) (x are IDs returned from the first query).

这篇关于MySQL JOIN/GROUP_CONCAT第二张表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！