GROUP BY - 不要将NULL分组 | 不要将NULL分组

本文介绍了GROUP BY - 不要将NULL分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

GROUP BY按预期工作，但我的问题是：Is有可能通过忽略NULL字段进行分组。所以它不会将NULL分组在一起，因为我仍然需要指定字段为NULL的所有行。

  SELECT`table1` 。*，
 GROUP_CONCAT（id SEPARATOR'，'）AS`children_ids` 
 FROM`table1` 
 WHERE（enabled = 1）
 GROUP BY`祖先`

现在让我们说我有5行并且祖先字段为NULL，它会返回我1行....但是我想要所有5个。

解决方案

也许你应该向空列添加一些东西，使它们独一无二，我正在寻找某种序列来代替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

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

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.

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.

解决方案

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分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！