有这样3张表任务表
ID Name
1 nm1
2 nm2已完成任务表
ID SwdID
1 1
2 1未完成任务
ID SwdID
1 1
2 1 已完成任务表和未完成任务表的SwdID字段对应的值是任务表中的ID如何用一条MYSQL语句将ID为1的任务名称、任务总数和已完成任务数获取出来?
ID Name
1 nm1
2 nm2已完成任务表
ID SwdID
1 1
2 1未完成任务
ID SwdID
1 1
2 1 已完成任务表和未完成任务表的SwdID字段对应的值是任务表中的ID如何用一条MYSQL语句将ID为1的任务名称、任务总数和已完成任务数获取出来?
select a.ID,a.Name,sum(b.id+c.id),sum(b.id)
from 任务表 a
left join 已完成任务表 b on a.id=b.SwdID
left join 未完成任务 c on a.id=c.SwdID
group by
a.ID,a.Name
(select count(*) from 已完成任务表 where ID=任务表.ID),
(select count(*) from 未完成任务 where ID=任务表.ID)
from 任务表
where id=1
from 任务表 a
left join 已完成任务表 b on a.id=b.SwdID
left join 未完成任务 c on a.id=c.SwdID
where a.id=1
group by
a.ID,a.Name
任务表:
mysql> select * from task;
+------+------+
| id | name |
+------+------+
| 1 | nm1 |
| 2 | nm2 |
+------+------+
2 rows in set (0.00 sec)
未完成任务表:
mysql> select * from wtask;
+------+------+
| id | tid |
+------+------+
| 1 | 1 |
| 2 | 1 |
+------+------+
2 rows in set (0.00 sec)
已完成任务表:
mysql> select * from ytask;
+------+------+
| id | tid |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+------+------+
mysql> select t1.*,t2.done,t2.done+t3.udone sumt
-> from task t1,
-> (select tid,count(*) done from ytask group by tid) t2,
-> (select tid,count(*) udone from wtask group by tid) t3
-> where t1.id=t2.tid and t1.id=t3.tid
-> and t1.id=1;
+------+------+------+------+
| id | name | done | sumt |
+------+------+------+------+
| 1 | nm1 | 3 | 5 |
+------+------+------+------+
1 row in set (0.00 sec)
(select count(*) from 已完成任务表 where ID=任务表.ID),
(select count(*) from 未完成任务 where ID=任务表.ID)
from 任务表
group by 任务表.id
a.Name,
count(b.id) as 任务总数,
sum(case when b.flag=1 then 1 else 0 end) as 已完成任务数
from
任务表 a
(select *,1 as flag from 已完成任务表
union all
select *,0 from 未完成任务
) b
on a.id=b.SwdID
group by a.Name
where a.id=1