MySQL计数和分组依据子查询
MySQL计数和分组依据子查询
提问于 2018-07-25 02:33:07
我正在尝试查找按分支和类别分组的帖子数量。我得不到计数为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回答 3
Stack Overflow用户
回答已采纳
发布于 2018-07-25 03:05:42
您需要首先交叉连接branches和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 (和几乎所有的聚合函数)忽略空值。
Stack Overflow用户
发布于 2018-07-25 03:00:17
看起来这可能是你想要的。
说明:获取帖子中存在的分支的每个可能的分支/类别组合。执行条件求和，以获得按日期范围和分支/类别的计数。然后再加入到分支。
SELECT b.b_id branch,
b.category,
COALESCE(Range_Sum.C1,0) C1,
COALESCE(Range_Sum.C2,0) C2
FROM ( SELECT b.id b_id,
c.id c_id,
c.category
FROM branches b,
categories c
WHERE EXISTS
( SELECT 1
FROM posts
WHERE b.id = posts.branch_id
)
) b
LEFT
JOIN (SELECT p.branch_id,
c.id c_id,
c.category,
SUM
( CASE WHEN p.created_at < '2018-01-01' THEN 1
ELSE 0
END
) C1,
SUM
( CASE WHEN p.created_at BETWEEN '2018-01-01' AND '2018-01-31' THEN 1
ELSE 0
END
) C2
FROM posts p
INNER
JOIN categories c
ON p.category_id = c.id
GROUP
BY p.branch_id,
c.category,
c.id
) Range_Sum
ON b.b_id = Range_Sum.branch_id
AND b.c_id = Range_Sum.c_id;此外，只需编写易于阅读的查询-永远不要使用x和y作为别名。选择任何其他可能提供更多信息的内容。
Stack Overflow用户
发布于 2018-07-25 07:52:08
也许有点做作..。
SELECT DISTINCT x.branch_id
, y.category_id
, COALESCE(z.created_at < '2018-01-01',0) c1
, COALESCE(z.created_at BETWEEN '2018-01-01' AND '2018-01-31',0) c2
FROM posts x
JOIN posts y
LEFT
JOIN posts z
ON z.branch_id = x.branch_id
AND z.category_id = y.category_id;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51505409
复制相关文章
相似问题