MySQL GROUP BY - 不要将 NULL 分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4588935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GROUP BY - do not group NULL
提问by slik
I'm trying to figure out a way to return results by using the group by function.
我试图找出一种通过使用 group by 函数来返回结果的方法。
GROUP BY is working as expected, but my question is: Is it possible to have group by ignore NULL field. So that it does not group NULLs together because I still need all the rows where the specified field is NULL.
GROUP BY 按预期工作，但我的问题是：是否可以通过忽略 NULL 字段来分组。这样它就不会将 NULL 组合在一起，因为我仍然需要指定字段为 NULL 的所有行。
SELECT `table1`.*,
GROUP_CONCAT(id SEPARATOR ',') AS `children_ids`
FROM `table1`
WHERE (enabled = 1)
GROUP BY `ancestor`
So now lets say I have 5 rows and ancestor field is NULL, it returns me 1 row....but I want all 5.
所以现在假设我有 5 行，祖先字段为 NULL，它返回我 1 行......但我想要所有 5 行。
回答by bot403
Perhaps you should add something to the null columns to make them unique and group on that? I was looking for some sort of sequence to use instead of UUID() but this might work just as well.
也许您应该向空列添加一些内容以使它们独一无二并对其进行分组？我正在寻找某种序列来代替 UUID() ，但这也可能同样有效。
SELECT `table1`.*,
IFNULL(ancestor,UUID()) as unq_ancestor
GROUP_CONCAT(id SEPARATOR ',') AS `children_ids`
FROM `table1`
WHERE (enabled = 1)
GROUP BY unq_ancestor
回答by Lightness Races in Orbit
When grouping by column Y, all rows for which the value in Yis NULLare grouped together.
当通过柱分组Y，所有的行为这在值Y就是NULL被分组在一起。
This behaviour is defined by the SQL-2003 standard, though it's slightly surprising because NULLis not equal to NULL.
这种行为是由 SQL-2003 标准定义的，尽管这有点令人惊讶，因为NULL它不等于NULL。
You can work around it by grouping on a different value, some function(mathematically speaking) of the data in your grouping column.
您可以通过对不同的值进行分组来解决这个问题，即分组列中数据的某些函数（从数学上讲）。
If you have a unique column Xthen this is easy.
如果您有一个独特的列，X那么这很容易。
Input
输入
X Y
-------------
1 a
2 a
3 b
4 b
5 c
6 (NULL)
7 (NULL)
8 d
Without fix
没有修复
SELECT GROUP_CONCAT(`X`)
FROM `tbl`
GROUP BY `Y`;
Result:
结果：
GROUP_CONCAT(`foo`)
-------------------
6,7
1,2
3,4
5
8
With fix
带修复
SELECT GROUP_CONCAT(`X`)
FROM `tbl`
GROUP BY IFNULL(`Y`, `X`);
Result:
结果：
GROUP_CONCAT(`foo`)
-------------------
6
7
1,2
3,4
5
8
Let's take a closer look at how this is working
让我们仔细看看这是如何工作的
SELECT GROUP_CONCAT(`X`), IFNULL(`Y`, `X`) AS `grp`
FROM `tbl`
GROUP BY `grp`;
Result:
结果：
GROUP_CONCAT(`foo`) `grp`
-----------------------------
6 6
7 7
1,2 a
3,4 b
5 c
8 d
If you don't have a unique column that you can use, you can try to generate a unique placeholder value instead. I'll leave this as an exercise to the reader.
如果您没有可以使用的唯一列，则可以尝试生成唯一的占位符值。我将把它作为练习留给读者。
回答by Hett
GROUP BY IFNULL(required_field, id)
GROUP BY IFNULL(required_field, id)
回答by ypercube??
SELECT table1.*,
GROUP_CONCAT(id SEPARATOR ',') AS children_ids
FROM table1
WHERE (enabled = 1)
GROUP BY ancestor
, CASE WHEN ancestor IS NULL
THEN table1.id
ELSE 0
END
回答by Stepan
Maybe faster version of previous solution in case you have unique identifier in table1 (let suppose it is table1.id) :
如果您在 table1 中有唯一标识符（假设它是 table1.id），则可能是先前解决方案的更快版本：
SELECT `table1`.*,
GROUP_CONCAT(id SEPARATOR ',') AS `children_ids`,
IF(ISNULL(ancestor),table1.id,NULL) as `do_not_group_on_null_ancestor`
FROM `table1`
WHERE (enabled = 1)
GROUP BY `ancestor`, `do_not_group_on_null_ancestor`
