GROUP BY - 不要将NULL分组 [英] GROUP BY - do not group 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分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！