未返回group_concat值为空的行 [英] Rows with null value for group_concat not returned

问题描述

我有以下MySQL查询，该查询应返回表a和b中的记录(一对多关系)，以及用逗号分隔的列表，其中列出了从表c返回的任何值.但是，表c中不会总是有记录(这就是为什么我使用LEFT OUTER JOIN将其联接到表a中的原因).

I've got the following MySQL query that's supposed to return records from table a and b (one to many relationship), and also a comma seperated list of any values returned from table c. However, there won't always be records in table c (which is why I'm using a LEFT OUTER JOIN to join it to table a).

SELECT  `a`.`id` , `a`.`name` , `b`.`id` AS  `b_id` , `b`.`name` AS  `b_name` , GROUP_CONCAT(  `c`.`l_id` ) AS  `c_ls`
FROM  `a`
INNER JOIN  `b` ON  `a`.`b_id` =  `b`.`id`
LEFT OUTER JOIN  `c` ON  `a`.`id` = `c`.`a_id`
GROUP BY `a`.`id`
ORDER BY  `a`.`created` DESC

上面的查询应返回2时返回1条记录.记录1在表c中具有3条匹配记录，记录2在表c中具有0条匹配记录.

The query above returns 1 record when it should return 2. Record 1 has 3 matching records in table c, record 2 has 0 matching records in table c.

查询返回:

id | name | b_id | b_name | c_ls
1  | John |   2  |  Bla   | [BLOB - 3 B]

如果我删除GROUP_CONCAT和GROUP_BY子句，则它将返回2条记录:

If I remove the GROUP_CONCAT and GROUP_BY clauses then it returns 2 records:

id | name | b_id | b_name | c_ls
1  | John |   2  |  Bla   | [BLOB - 3 B]
2  | Fred |   3  |  Blo   | [BLOB - NULL]

似乎如果c_ls为null，则GROUP_CONCAT阻止返回该行.关于我在做什么错有什么想法吗?

It seems that if c_ls is null then GROUP_CONCAT stops the row from being returned. Any thoughts as to what I'm doing wrong?

推荐答案

不幸的是，以前标记为正确的答案是错误的(如用户desaivv在评论中指出的那样).

The answer previously marked as right is unfortunately wrong (as user desaivv noted in the comment).

它必须读取 IFNULL ，[不是ISNULL，isnull只接受一个参数并返回一个布尔值]！

It must read IFNULL, [not ISNULL, isnull just takes one parameter and returns a boolean] !

IFNULL如果为null，则返回第二个参数:

IFNULL returns the second paramter if null:

SELECT  `a`.`id` , `a`.`name` , `b`.`id` AS  `b_id` , `b`.`name` AS  `b_name` ,   
IFNULL(GROUP_CONCAT(  `c`.`l_id` ), '') AS  `c_ls`
FROM  `a`
INNER JOIN  `b` ON  `a`.`b_id` =  `b`.`id`
LEFT OUTER JOIN  `c` ON  `a`.`id` = `c`.`a_id`
GROUP BY `a`.`id`
ORDER BY  `a`.`created` DESC

但这根本不是解决方案！ 我们需要的是复杂的"联接-

But this is not the solution at all! What we need is a "convoluted" join -

因此，请检查此SQL Fiddle: http://www.sqlfiddle.com/#!2/54c6f/3/0

这篇关于未返回group_concat值为空的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！