使用mysql在一个查询中选择多个表中的所有子项

Question

我所有的下午都花在尝试处理一个查询（或两个或三个）上，以获取三个表的所有子表的计数。看一下我的设计：

用户表

id_user | name
1 | foo
2 | bar

胜表

id_won | user
1 | 1
2 | 1
3 | 2

画表

id_draw | user
1 | 1
2 | 2
3 | 2

输桌子

id_lose | user
1 | 1
2 | 1
3 | 1

我试图得到这样的东西：

name | wons | draws | loses
foo | 2 | 1 | 3
bar | 1 | 2 | 0

这是我的尝试：

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;

Answer 1

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

这只是完成任务所需的工作量。