GROUP BY - 不要将 NULL 分组

GROUP BY - do not group NULL(GROUP BY - 不要将 NULL 分组)

问题描述

我正在尝试找出一种使用 group by 函数返回结果的方法.

I'm trying to figure out a way to return results by using the group by function.

GROUP BY 按预期工作，但我的问题是:是否可以通过忽略 NULL 字段来创建组.这样它就不会将 NULL 组合在一起，因为我仍然需要指定字段为 NULL 的所有行.

GROUP BY is working as expected, but my question is: Is it possible to have a group by ignoring the NULL field. So that it does not group NULLs together because I still need all the rows where the specified field is NULL.

SELECT `table1`.*, 
    GROUP_CONCAT(id SEPARATOR ',') AS `children_ids`
FROM `table1` 
WHERE (enabled = 1) 
GROUP BY `ancestor` 

所以现在假设我有 5 行并且祖先字段为 NULL，它返回我 1 行......但我想要所有 5 行.

So now let's say I have 5 rows and the ancestor field is NULL, it returns me 1 row....but I want all 5.

推荐答案

也许您应该向空列添加一些内容以使它们独一无二并以此为基础进行分组?我正在寻找某种序列来代替 UUID() ，但这可能也能正常工作.

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.

SELECT `table1`.*, 
    IFNULL(ancestor,UUID()) as unq_ancestor
    GROUP_CONCAT(id SEPARATOR ',') AS `children_ids`
FROM `table1` 
WHERE (enabled = 1) 
GROUP BY unq_ancestor

这篇关于GROUP BY - 不要将 NULL 分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！