我有以下三个表：
任务表-task_id，activity_id（FOREIGN KEY），开始日期，结束日期
活动表-activity_id，Milestone_id（FOREIGN KEY），other_fields
里程碑表-milestone_id，project_id，other_fields
现在我想要这样的结果集
No_of_task(count) | no_activity | milestone_name
5 : 2 : ABC Milestone
我想计算end_dated任务，根据end_date任务和里程碑名称计算活动。
例如，具有5个活动和20个不同任务的1个里程碑名称Housing_Construction ... end_dated一些任务是这样
像这样的housing_construction活动的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 | |
+-----------------------+-------------+------+-----+---------+----------------+
活动表说明
+-----------------------+-------------+------+-----+---------+----------------+
| 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 | |
+-----------------------+-------------+------+-----+---------+----------------+
最佳答案
尝试这个：
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;