mysql - 使用mysql在一个查询中选择多个表中的 child 总数

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

丢 table

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

您可以在子查询中计算它们的总值并将它们连接到表 users 中。我添加了 COALESCE 以显示 zero 而不是 null 以防 user 不在其他表中。

SELECT  a.id_user,
        COALESCE(b.totalWon,0) Wons,
        COALESCE(d.totalLoses,0) Loses,
        COALESCE(c.totalDraws,0) Draws
FROM    users a
        LEFT JOIN 
        (
            SELECT `user`, COUNT(id_won) totalWon
            FROM wons
            GROUP BY `user`
        ) b ON a.id_user = b.`user`
        LEFT JOIN
        (
            SELECT `user`, COUNT(id_draw) totalDraws
            FROM draws
            GROUP BY `user`
        ) c ON a.id_user = c.`user`
        LEFT JOIN
        (
            SELECT `user`, COUNT(id_lose) totalLoses
            FROM loses
            GROUP BY `user`
        ) d ON a.id_user = d.`user`

SQLFiddle Demo