GROUP_CONCAT和LEFT_JOIN问题-并非所有行都返回 [英] GROUP_CONCAT and LEFT_JOIN issue - Not all rows returned

问题描述

假设我的数据库方案如下:

Let's say my DB Scheme is as follows:

T_PRODUCT
id_product (int, primary)

two entries: (id_product =1) , (id_product =2)

T_USER
id_user (int, primary)
id_product (int, foreign key)
name_user (varchar)

two entries: (id_product=1,name_user='John') , (id_product=1,name_user='Mike')

如果我运行第一个查询来获取所有产品及其用户(如果有)，我将得到:

If I run a first query to get all products with their users (if there are any), I get this:

SELECT T_PRODUCT.id_product, T_USER.name_user 
FROM T_PRODUCT
LEFT JOIN T_USER on T_USER.id_product = T_PRODUCT.id_product;

>>
id_product name_user
1          John
1          Mike
2          NULL

对我很好. 现在，如果我想做同样的事情，除了我希望每行有一个产品，并使用串联的用户名(如果有任何用户，否则为NULL):

Looks good to me. Now if I want to the same thing, except I'd like to have one product per line, with concatenated user names (if there are any users, otherwise NULL):

SELECT T_PRODUCT.id_product, GROUP_CONCAT(T_USER.name_user) 
FROM T_PRODUCT
LEFT JOIN T_USER on T_USER.id_product = T_PRODUCT.id_product;

>>
id_product name_user
1          John,Mike

**expected output**:
id_product name_user
1          John,Mike
2          NULL

如果没有产品用户，即使存在 LEFT JOIN ，GROUP_CONCAT也会阻止mysql为该产品生产线.

If there are no users for a product, the GROUP_CONCAT prevents mysql from producing a line for this product, even though there's a LEFT JOIN.

1. 这是预期的MySQL行为吗?
2. 有什么办法可以使用GROUP_CONCAT或其他函数来获得预期的输出吗?

推荐答案

啊，找到了我的答案:

使用 GROUP_CONCAT 时，您永远不会忘记 GROUP BY 子句. 我在第二个查询中缺少GROUP BY T_PRODUCT.id_product.

You should never forget the GROUP BY clause when working with GROUP_CONCAT. I was missing GROUP BY T_PRODUCT.id_product in my second query.

万一有人像我一样心不在a，我会把问题抛在脑后.

I'll leave the question up in case someone is as absent-minded as I am.

编辑:

从此答案，我认为我也可以激活SQL模式 强制MySQL在GROUP BY丢失或不正确的情况下抛出错误.

From this answer, I figured I can also activate the SQL Mode ONLY_FULL_GROUP_BY to force MySQL to throw an error in case the GROUP BY is missing or incorrect.

这篇关于GROUP_CONCAT和LEFT_JOIN问题-并非所有行都返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！