使用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;

4 个解决方案

#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

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

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

#2

You can calculated their total values in a subquery and joined them on table users. I added COALESCE to show zero instead of null in the case the user is not present on the other tables.

您可以在子查询中计算它们的总值，并在表用户中联接它们。我添加了COALESCE，以便在用户不在其他表上的情况下显示为零，而不是null。

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

#3

The cleanest SQL is:

最干净的SQL是:

SELECT 
    u.name,
    w.wins,
    d.draws,
    l.loses
FROM
    user u
LEFT JOIN 
    (SELECT user,COUNT(*) wins FROM wons GROUP BY user) w ON w.user = u.id_user
LEFT JOIN 
    (SELECT user,COUNT(*) draws FROM draws GROUP BY user) d ON d.user = u.id_user
LEFT JOIN 
    (SELECT user,COUNT(*) loses FROM loses GROUP BY user) l ON l.user = u.id_user

See http://sqlfiddle.com/#!2/91b61/10

看到http://sqlfiddle.com/ ! 2/91b61/10

#4

I'll suggest an alternative approach which might be an overkill for your project but keeps performance in mind if you are going to generate the desired result a lot.

我将建议一种替代方法，这种方法可能对您的项目造成过多的影响，但如果您想要大量生成所需的结果，请记住性能。

Create a summary table (similar to your desired output). You can empty shell record created for each user there.

创建一个汇总表(类似于您想要的输出)。您可以为那里的每个用户创建空shell记录。

Add triggers in won, draw and losses table that simply updated the data in summary table.

在win、draw和loss表中添加触发器，只更新汇总表中的数据。

That's one approach. Next approach is if your desired result isn't mission critical (i.e. does not have to be an up to date thing) then consider adding a scheduled events (that run every 10 or 20 min that run the query provided above (which I've given +1 votes) and use that to update the summary table.

这是一个方法。下一个方法是如果你想要的结果不是关键任务(即不需要一个更新的东西)然后可以考虑添加一个预定的事件(每10或20分钟跑上面提供的查询(我给+ 1票),用它来更新汇总表。

Best of luck!

最好的运气!