用连接表中的两个计数编写连接查询

I've spent a few hours fighting with this, but I can't get the counts to work. Hopefully someone can help?!

我花了几个小时和这个做斗争，但我不能让计数工作。希望有人可以帮忙吗? !

I have a project table and task table, linked on the project_id. I can get the project_id, project_name, and the status_id with the query below:

我有一个项目表和任务表，链接在project_id上。我可以获得project_id、project_name和status_id，查询如下:

SELECT 
    a.project_id, 
    a.project_name,
    b.status_id
FROM project_list as a
INNER JOIN task_list as b
ON a.project_id=b.project_id

I'd like to select a single record for each project and add two count fields based on the status_id. In pseudo code:

我想为每个项目选择一条记录，并基于status_id添加两个计数字段。在伪代码:

SELECT 
    a.project_id, 
    a.project_name,
    (SELECT COUNT(*) FROM task_list WHERE status_id < 3) as not_completed,
    (SELECT COUNT(*) FROM task_list WHERE status_id = 3) as completed
FROM project_list as a
INNER JOIN task_list as b
ON a.project_id=b.project_id
GROUP BY project_id

My create table scripts are below:

我的create table脚本如下:

CREATE TABLE `project_list` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `topic_id` int(11) DEFAULT NULL,
  `project_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`project_id`)
)

CREATE TABLE `task_list` (
  `task_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `task_name` varchar(45) DEFAULT NULL,
  `status_id` int(11) DEFAULT '0',
  PRIMARY KEY (`task_id`)
)

Any help is much appreciated. Thanks!

非常感谢您的帮助。谢谢!

EDIT: ANSWER:

编辑:答:

SELECT 
    a.project_id, 
    project_name,
    SUM(status_id != 3) AS not_completed,
    SUM(status_id = 3) AS completed,
    SUM(status_id IS NOT NULL) as total
FROM tasks.project_list as a
INNER JOIN tasks.task_list as b
ON a.project_id=b.project_id
GROUP BY a.project_id

1 个解决方案

#1

The problem is that in your subqueries you are counting all the rows in the whole table rather than just the rows that have the correct project_id. You could fix this by modifying the WHERE clause in each of your subqueries.

问题是，在子查询中，您要计算整个表中的所有行，而不是只计算具有正确project_id的行。您可以通过修改每个子查询中的WHERE子句来修复这个问题。

(SELECT COUNT(*)
 FROM task_list AS c
 WHERE c.status_id < 3
 AND a.project_id = c.project_id)

However a simpler approach is to use SUM with a boolean condition instead of COUNT to count the rows that match the condition:

但是一种更简单的方法是使用带有布尔条件的SUM而不是COUNT来计算符合条件的行数:

SELECT 
    a.project_id, 
    a.project_name,
    SUM(b.status_id < 3) AS not_completed,
    SUM(b.status_id = 3) AS completed,
FROM project_list as a
INNER JOIN task_list as b
ON a.project_id = b.project_id
GROUP BY project_id

This works because TRUE evaluates to 1 and FALSE evaluates to 0.

这是因为TRUE的值是1,FALSE的值是0。

#1