如何将这两个查询合并为一个？ （针对同一个表的多个连接）

Given two tables, one for workers and one for tasks completed by workers,

给出两个表,一个用于工人,一个用于工人完成的任务,

CREATE TABLE IF NOT EXISTS `workers` (
  `id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `workers` (`id`) VALUES
(1);


CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL,
  `worker_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `tasks` (`id`, `worker_id`, `status`) VALUES
(1, 1, 1),
(2, 1, 1),
(3, 1, 2),
(4, 1, 2),
(5, 1, 2);

I'm trying to get the number of tasks each worker has with each status code.

我正在尝试获取每个工作人员拥有的每个状态代码的任务数量。

I can say either

我也可以说

SELECT w.*
,COUNT(t1.worker_id) as status_1_count
FROM workers w
LEFT JOIN tasks t1 ON w.id = t1.worker_id AND t1.status = 1 
WHERE 1 
GROUP BY 
t1.worker_id
ORDER BY w.id

or

SELECT w.*
,COUNT(t2.worker_id) as status_2_count
FROM workers w
LEFT JOIN tasks t2 ON w.id = t2.worker_id AND t2.status = 2
WHERE 1 
GROUP BY 
t2.worker_id
ORDER BY w.id

and get the number of tasks with a single given status code, but when I try to get the counts for multiple task statuses in a single query, it doesn't work!

并获得具有单个给定状态代码的任务数量,但是当我尝试在单个查询中获取多个任务状态的计数时,它不起作用!

SELECT w.*
,COUNT(t1.worker_id) as status_1_count
,COUNT(t2.worker_id) as status_2_count
FROM workers w
LEFT JOIN tasks t1 ON w.id = t1.worker_id AND t1.status = 1 
LEFT JOIN tasks t2 ON w.id = t2.worker_id AND t2.status = 2
WHERE 1 
GROUP BY t1.worker_id
,t2.worker_id
ORDER BY w.id

The tasks table is cross-joining against itself when I would rather it wouldn't!

任务表是反对自己的,当我宁愿它不会!

Is there any way to combine these two queries into one such that we can retrieve the counts for multiple task statuses in a single query?

有没有办法将这两个查询组合成一个,以便我们可以在一个查询中检索多个任务状态的计数?

Thanks!

3 个解决方案

#1

SELECT w.*,
  SUM(t1.status = 1) AS status_1_count,
  SUM(t1.status = 2) AS status_2_count
FROM workers w
  LEFT JOIN tasks t1 ON w.id = t1.worker_id AND t1.status IN (1, 2) 
GROUP BY w.id
ORDER BY w.id;

#2

I'm trying to get the number of tasks each worker has with each status code.

我正在尝试获取每个工作人员拥有的每个状态代码的任务数量。

SELECT worker_id, status, COUNT(*)
    FROM tasks
    GROUP BY worker_id, status;

That's all.

#3

I don't have an instance of MySQL here, but I tested this on a t-sql box and it worked.

我这里没有MySQL的实例,但是我在t-sql盒子上测试过它并且它有效。

select distinct(worker_id), 
       (select count(*) from tasks where status = 1) as Status1, 
       (select count(*) from tasks where status = 2) as Status2 
   from tasks;