java - 如何使用三个表编写这个复杂的 MySQL 查询？

Question

我有以下三个表:

1. 任务表 -- task_id、activity_id(FOREIGN KEY)、start_date、end_date

2. Activity 表——activity_id、milestone_id(FOREIGN KEY)、other_fields

3. 里程碑表 -- Milestone_id、project_id、other_fields

现在我想要这样的结果集

No_of_task(count) | no_activity | milestone_name
      5           :     2       :    ABC Milestone

我想计算 end_dated 任务，并根据 end_date 任务和里程碑名称计算 Activity 。

例如，1 个里程碑名称 Housing_Construction，有 5 个 Activity 和 20 个不同的任务...某些任务 end_dated 如此

如下所示，housing_construction 的 3 个 Activity 的 5 个任务结束。

任务表说明

+-----------------------+-------------+------+-----+---------+----------------+
| Field                 | Type        | Null | Key | Default | Extra          |
+-----------------------+-------------+------+-----+---------+----------------+
| activity_task_id      | int(11)     | NO   | PRI | NULL    | auto_increment |
+-----------------------+-------------+------+-----+---------+----------------+
| milestone_activity_id | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| task                  | varchar(50) | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| description           | text        | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| assign_to_employee_id | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| assign_date           | datetime    | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| task_end_date         | date        | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| tasktime              | varchar(50) | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| status                | tinyint(1)  | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+
| is_delete             | tinyint(1)  | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+
| entry_employee_id     | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| edit_employee_id      | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| createddatetime       | datetime    | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| modifydatetime        | datetime    | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| nooftimesedit         | smallint(6) | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+
| completion_date       | datetime    | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+

Activity 表描述

+-----------------------+-------------+------+-----+---------+----------------+
| Field                 | Type        | Null | Key | Default | Extra          |
+-----------------------+-------------+------+-----+---------+----------------+
| milestone_activity_id | int(11)     | NO   | PRI | NULL    | auto_increment |
+-----------------------+-------------+------+-----+---------+----------------+
| project_milestone_id  | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| activityname          | varchar(50) | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| activity_end_date     | date        | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| activity_description  | text        | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| status                | tinyint(1)  | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+
| is_delete             | tinyint(1)  | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+
| entry_employee_id     | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| edit_employee_id      | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| createddatetime       | datetime    | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| modifydatetime        | datetime    | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| nooftimesedit         | smallint(6) | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+

里程碑表说明

+-----------------------+-------------+------+-----+---------+----------------+
| Field                 | Type        | Null | Key | Default | Extra          |
+-----------------------+-------------+------+-----+---------+----------------+
| project_milestone_id  | int(11)     | NO   | PRI | NULL    | auto_increment |
+-----------------------+-------------+------+-----+---------+----------------+
| project_id            | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| milestone             | varchar(50) | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| milestone_description | text        | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| milestone_end_date    | date        | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| status                | tinyint(1)  | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+
| is_delete             | tinyint(1)  | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+
| entry_employee_id     | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| edit_employee_id      | int(11)     | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| createddatetime       | datetime    | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| modifydatetime        | datetime    | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
| nooftimesedit         | smallint(6) | YES  |     | 0       |                |
+-----------------------+-------------+------+-----+---------+----------------+
| noofdays              | int(11)     | YES  |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+

Answer 1

试试这个:

select 
    count(distinct t.activity_task_id) No_of_task,
    count(distinct a.milestone_activity_id) no_activity,
    m.milestone_description milestone_name
from milestone m
inner join activity a
    on m.project_milestone_id = a.project_milestone_id
inner join task t
    on a.milestone_activity_id = t.milestone_activity_id
where t.status != 4
and t.task_end_date < curdate()
group by m.project_milestone_id,
    m.milestone_description;