时间戳

首页 > 解决方案 > MySQL COUNT 不同总数，其中 ALL/EVERY 加入 = 给定值

mysql - MySQL COUNT 不同总数，其中 ALL/EVERY 加入 = 给定值

问题描述

我试图用简单的示例数据尽可能地简化这一点。我有一个名为 Assignments 的表和一个名为 Tasks 的表：

作业

+---------------+------------------+
| ASSIGNMENT_ID |  ASSIGNMENT_NAME |
+---------------+------------------+
|               |                  |
| 1             |  Morning Tasks   |
|               |                  |
| 2             |  Afternoon Tasks |
|               |                  |
| 3             |  Evening Tasks   |
+---------------+------------------+

任务

+---------+----------------+----------------+--------------+
| TASK_ID |  ASSIGNMENT_ID |    TASK_NAME   |  TASK_STATUS |
+---------+----------------+----------------+--------------+
|         |                |                |              |
| 1       |  1             |  Make Bed      |  2           |
|         |                |                |              |
| 2       |  1             |  Brush Teeth   |  2           |
|         |                |                |              |
| 3       |  1             |  Eat Breakfast |  2           |
|         |                |                |              |
| 4       |  1             |  Commute       |  2           |
|         |                |                |              |
| 5       |  2             |  Eat Lunch     |  2           |
|         |                |                |              |
| 5       |  2             |  Wash Dishes   |  1           |
|         |                |                |              |
| 6       |  2             |  Wash Table    |  1           |
|         |                |                |              |
| 7       |  3             |  Make Supper   |  1           |
|         |                |                |              |
| 8       |  3             |  Eat Supper    |  1           |
|         |                |                |              |
| 9       |  3             |  Wash Dishes   |  1           |
+---------+----------------+----------------+--------------+

任务状态中，1表示未完成，2表示完成。

对于每个作业，都有相关的任务。我想计算所有相关任务都已完成的所有分配（TASK_STATUS = 2）（在我的实际用例中，也满足许多其他标准，但这不在此示例范围内。）

所以在这个例子中，答案应该是"1"。不是结果列表，只是它们的数量。（只是上午的任务完成了，下午的任务已经开始了，但并不是全部都完成了。）

我不想知道有多少任务完成了，或者有多少任务已经开始，只是有多少任务是 100% 完成的。

我尝试了几种方法，但我遇到了心理障碍......

这将为我提供已完成任务的所有作业

SELECT COUNT(a.assignment_id) AS completed_assignments
FROM assignments AS a
JOIN tasks AS t ON a.assignment_id = t.assignment_id 
WHERE t.task_status = 2
LIMIT 1;

这也将为我提供已完成任务的所有作业

SELECT COUNT(DISTINCT(CASE WHEN t.task_status = 2 THEN t.assignment_id ELSE NULL END)) AS completed_assignments
FROM assignments AS a
JOIN tasks AS t ON a.assignment_id = t.assignment_id 
WHERE t.task_status = 2
LIMIT 1;

但我想要所有任务都完成的地方。（只是计算完成的数量，而不是所有结果的列表。）

这将给出结果的数量（在结果行中，我可以计算），但我需要查询中的值（用于执行其他计算）

SELECT a.assignment_id,
(COUNT(CASE WHEN t.task_status = 2 THEN t.task_id ELSE NULL END)/COUNT(t.task_id)*100) AS percent_complete
FROM assignments AS a
JOIN tasks AS t ON a.assignment_id = t.assignment_id 
GROUP BY a.assignment_id 
HAVING percent_complete = 100;

结果数等于总数

有任何想法吗？

标签： mysqldatabasejoincounthaving-clause

解决方案

你可以用all count = count case when status = 2它来做。 

select * 
from Assignments
where ASSIGNMENT_ID  in (
    select 
        ASSIGNMENT_ID 
    from Tasks
    group by ASSIGNMENT_ID
    having count(1) = count(case when TASK_STATUS = '2' then 1 end)
)

更新 ：

感谢您的努力，但我正在寻找已完成的计数，而不是列表

CREATE TABLE Tasks
  (`TASK_ID` int, `ASSIGNMENT_ID` int, `TASK_NAME` varchar(13), `TASK_STATUS` int)
;

INSERT INTO Tasks
  (`TASK_ID`, `ASSIGNMENT_ID`, `TASK_NAME`, `TASK_STATUS`)
VALUES
  (NULL, NULL, NULL, NULL),
  (1, 1, 'Make Bed', 2),
  (NULL, NULL, NULL, NULL),
  (2, 1, 'Brush Teeth', 2),
  (NULL, NULL, NULL, NULL),
  (3, 1, 'Eat Breakfast', 2),
  (NULL, NULL, NULL, NULL),
  (4, 1, 'Commute', 2),
  (NULL, NULL, NULL, NULL),
  (5, 2, 'Eat Lunch', 2),
  (NULL, NULL, NULL, NULL),
  (5, 2, 'Wash Dishes', 2),
  (NULL, NULL, NULL, NULL),
  (6, 2, 'Wash Table', 2),
  (NULL, NULL, NULL, NULL),
  (7, 3, 'Make Supper', 1),
  (NULL, NULL, NULL, NULL),
  (8, 3, 'Eat Supper', 1),
  (NULL, NULL, NULL, NULL),
  (9, 3, 'Wash Dishes', 1)
;

CREATE TABLE Assignments
  (`ASSIGNMENT_ID` int, `ASSIGNMENT_NAME` varchar(15))
;

INSERT INTO Assignments
  (`ASSIGNMENT_ID`, `ASSIGNMENT_NAME`)
VALUES
  (NULL, NULL),
  (1, 'Morning Tasks'),
  (NULL, NULL),
  (2, 'Afternoon Tasks'),
  (NULL, NULL),
  (3, 'Evening Tasks')
;

select count(1) completedCount
from (
    select 
        ASSIGNMENT_ID 
    from Tasks
    group by ASSIGNMENT_ID
    having count(1) = count(case when TASK_STATUS = '2' then 1 end)
) T

| 完成计数 |
| -------------: |
| 2 |

db<>在这里摆弄

推荐阅读