MySQL COUNT和GROUP BY子查询

5r33naDh

我正在尝试查找按分支和类别分组的帖子数。我没有得到计数为0的类别。

CREATE TABLE branches
    (`id` serial primary key, `name` varchar(7) unique)
;

INSERT INTO branches
    (`id`, `name`)
VALUES
    (1, 'branch1'),
    (2, 'branch2'),
    (3, 'branch3')
;


CREATE TABLE categories
    (`id` serial primary key, `category` varchar(4) unique)
;

INSERT INTO categories
    (`id`, `category`)
VALUES
    (1, 'cat1'),
    (2, 'cat2')
;


CREATE TABLE posts
    (`id` serial primary key, `branch_id` int, `category_id` int, `title` varchar(6), `created_at` varchar(10))
;

INSERT INTO posts
    (`id`, `branch_id`, `category_id`, `title`, `created_at`)
VALUES
    (1, 1, 1, 'Title1', '2017-12-14'),
    (2, 1, 2, 'Title2', '2018-01-05'),
    (3, 2, 1, 'Title3', '2018-01-10')
;

预期产量：

+---------+----------+----+----+
| branch  | category | c1 | c2 |
+---------+----------+----+----+
| branch1 | cat1     |  1 |  0 |
| branch1 | cat2     |  0 |  1 |
| branch2 | cat1     |  0 |  1 |
| branch2 | cat2     |  0 |  0 |
+---------+----------+----+----+

查询尝试：

SELECT b.name, x.c1, y.c2 FROM branches b
LEFT JOIN (
     SELECT COUNT(id) c1 FROM posts WHERE created_at < '2018-01-01'
     GROUP BY posts.branch_id, posts.category_id
) x x.branch_id = b.id
LEFT JOIN (
     SELECT COUNT(id) c2 FROM posts WHERE created_at BETWEEN '2018-01-01' AND '2018-01-31'
     GROUP BY posts.branch_id, posts.category_id
) y y.branch_id = b.id
GROUP BY b.id

于尔多

您需要CROSS JOINbranches和categories第一; 然后向左加入posts并根据您的WHERE标准进行条件计数。

通用格式：

SELECT x.data, y.data
, COUNT(CASE WHEN conditionN THEN 1 ELSE NULL END) AS cN
FROM x CROSS JOIN y
LEFT JOIN z ON x.id = z.x_id AND y.id = z.y_id
GROUP BY x.data, y.data
;

注意：COUNT（几乎所有聚合函数）都会忽略NULL值。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。