如何在一个 SQL 问题中计算 NULL 类别

发布于 2024-08-25 23:13:57 字数 297 浏览 4 评论 0原文

我有一个博客应用程序，帖子属于类别和类别有许多帖子

帖子可以有类别，也可以没有 - 在后一种情况下，Post.category_id 字段中存在 NULL 值。

现在我想使用单个 SQL 查询来计算以下类别计数

category|post_count
--------------
PHP | 2
JavaScript | 4
SomeOtherCat | 1
NULL | 3

这里的线索是我还想计算没有类别的帖子（上面的 NULL 行）。是否可以通过一个 SQL 查询来实现？

原文

i have a blog application were Post belongsTo Category and Category hasMany Post

Post can have a Category or not - in latter case NULL value is present in Post.category_id field.

Now i would like to have following category count with single SQL query

category|post_count
--------------
PHP | 2
JavaScript | 4
SomeOtherCat | 1
NULL | 3

The clue here is that i also want to count posts without category (NULL row above). Is it posibble with one SQL query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寂寞笑我太脆弱 2024-09-01 23:13:58

SELECT
  c.CategoryName,
  COUNT(*)
FROM
  Posts p
    left join
  Category c
    on
      p.Category_id = c.Category_id
group by
  c.CategoryName

（出于分组依据的目的，所有 NULL 结果都进入同一组。这对于那些在编写条件时只习惯 NULL != NULL 的人来说可能会感到惊讶）

SELECT
  c.CategoryName,
  COUNT(*)
FROM
  Posts p
    left join
  Category c
    on
      p.Category_id = c.Category_id
group by
  c.CategoryName

(For the purposes of group by, all NULL results go into the same group. This can be surprising to some who are only used to the fact that NULL != NULL when writing conditions)

回复收藏 0 原文

多情出卖 2024-09-01 23:13:58

它应该适用于这样的内容：

SELECT C.category, COUNT(*) AS post_count
FROM Post P
LEFT JOIN Category C ON P.category_id=C.category_id
GROUP BY C.category

我不确定类别中的标识列，您可能需要替换为正确的名称。

编辑：哎呀 - 忘记了分组依据。

It should work with something like this:

SELECT C.category, COUNT(*) AS post_count
FROM Post P
LEFT JOIN Category C ON P.category_id=C.category_id
GROUP BY C.category

I'm not sure about the identity column in Category, you might need to replace with the correct name.

Edit: Oops - forgot the group by.

回复收藏 0 原文

￡冰雨忧蓝° 2024-09-01 23:13:58

SELECT C.CategoryName, Count(P.Post_ID)
FROM Category C INNER JOIN Posts P
ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName
UNION
SELECT NULL, Count(P.Post_ID)
FROM Posts P
WHERE P.CategoryID IS NULL
GROUP BY NULL

SELECT C.CategoryName, Count(P.Post_ID)
FROM Category C INNER JOIN Posts P
ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName
UNION
SELECT NULL, Count(P.Post_ID)
FROM Posts P
WHERE P.CategoryID IS NULL
GROUP BY NULL

回复收藏 0 原文

~没有更多了~