mysql 查询中包含总数,使用mysql在一个查询中在多个表中选择总数

All my afternoon was consumed trying to deal with a query (or two or three) in order to get the count of all childs of three tables. Take a look to my design:

user table

id_user | name

1 | foo

2 | bar

wons table

id_won | user

1 | 1

2 | 1

3 | 2

draws table

id_draw | user

1 | 1

2 | 2

3 | 2

loses table

id_lose | user

1 | 1

2 | 1

3 | 1

I'm trying to get something like this:

name | wons | draws | loses

foo | 2 | 1 | 3

bar | 1 | 2 | 0

This is my try:

select

u.name, w.total_w, d.total_d, l.total_l

from

user u

LEFT JOIN

(select count(user) as total_w, user from wons group by user) as w

ON w.user = u.id_user

LEFT JOIN

(select count(user) as total_d, user from draws group by user) as d

ON d.user = w.user

LEFT JOIN

(select count(user) as total_l, user from loses group by user) as l

ON d.user= .user

group by u.id_user;

解决方案select u.name, w.uw as wins, l.ul as loses, d.ud as draws from user

left join (select user, COUNT(id_won) uw from wons group by user) w on w.user = u.user_id

left join (select user, COUNT(id_lose) ul from loses group by user) l on l.user = u.user_id

left join (select user, COUNT(id_draw) ud from draws group by user) d on d.user = u.user_id

This one would just the needed amount of work for the task.