ÇóÒ»ÌõMySqlÓï¾ä

ÓÐÕâÑù3Õűí

ÈÎÎñ±í
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 a.ID,a.Name,sum(COALESCE(b.id,0)+COALESCE(c.id,0)),sum(COALESCE(b.id,0))
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

---

SQL code:

ÈÎÎñ±í£º
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 |
+------+--