如何对 NULL 字段进行分组

Question

在这个查询之后：

SELECT forum_categories.id AS category_id, forum_categories.title, forum_topics.id AS topic_id, forum_topics.title, user
FROM forum_categories JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6
ORDER BY forum_categories.id

我得到了这个结果：

现在，我想：

• 在选择名为 thereIsANull
• 按字段 user category_id
• 设置 thereIsANull 对我的所有结果进行分组到如果 user 字段中不存在 NULL（分组时），则为 1，否则为 0。

因此，在下面的示例中，结果必须为 1 行：

6    Welcome    (some topic_id)     (some title)   (djfonplaz or null)   0

并且，如果所有用户都不同于 NULL：

6    Welcome    (some topic_id)     (some title)   (djfonplaz)   1

我该如何在 MySql 上执行此操作？

Answer 1

SELECT 
  forum_categories.id AS category_ids
  , forum_categories.title as titles
  , forum_topics.id AS topic_ids
  , forum_topics.title as topic_titles
  , count(*) as NumberOfRows
  , GROUP_CONCAT(IFNULL(user,'(no user)')) AS users 
  , (count(*) - count(user)) as IsThereANull  
FROM forum_categories 
INNER JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6
GROUP BY category_id
ORDER BY forum_categories.id

如果不存在 null，则 IsThereANull 将为 0；如果存在，则为 1 或更多。

Answer 2

我认为以下查询应该得到您正在寻找的结果：

SELECT forum_categories.id AS category_id, forum_categories.title, 
       forum_topics.id AS topic_id, forum_topics.title, user, 
       (
          SELECT MAX(IF(COUNT(*), 0, 1))
          FROM forum_categories JOIN forum_topics ON 
               forum_categories.id=forum_topics.category_id
          LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
          WHERE forum_categories.id=6 AND user IS NULL
    ) AS thereIsANull
FROM forum_categories JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6 AND user IS NOT NULL
GROUP BY user
ORDER BY forum_categories.id

Answer 3

我认为这样的事情会如您所愿：

SELECT cat.id AS category_id, 
       cat.title, 
       top.id AS topic_id, 
       top.title, 
       user,
       (
           (
               SELECT COUNT(*) FROM forum_categories 
               WHERE id = cat.id AND user IS NULL
           ) > 0 -- This is to check wether if any NULL is found
       ) as thereIsANull 
FROM forum_categories as cat
JOIN forum_topics as top ON cat.id = top.category_id
LEFT OUTER JOIN forum_views as view ON top.id = view.topic_id
WHERE cat.id = 6 -- You can ofcourse change this to whatever variable your using
GROUP BY cat.id -- Just an ordinary group by category id
ORDER BY cat.id

假设在forum_categories表中找到了字段用户，否则只需修改子查询以加入您从中获取用户的表

请注意，Thiis基本上与< a href="https://stackoverflow.com/users/263671/cez">Cez 写道，所以他值得一些功劳，唯一的区别是子查询和按类别 id 而不是用户分组。

尝试一下，让我知道它是否正确:)