mysql join查询数量_mysql-从联接表中写入两个计数的JOIN查询

我花了几个小时来解决这个问题,但是我无法胜任工作.希望有人可以提供帮助？

我有一个项目表和任务表,链接在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

我想为每个项目选择一条记录,并根据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

我的创建表脚本如下：

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`)

)

任何帮助深表感谢.谢谢！

编辑：答案：

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