代码之家 › 专栏 › 技术社区 › manix

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

multiple-tables count sql mysql

manix · 技术社区 · 12 年前

我整个下午都在努力处理一个查询(或两三个),以获得三个表的所有子项的计数。看看我的设计:

用户表

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;

4 回复 | 直到 12 年前

John Woo 12 年前

您可以在子查询中计算它们的总值,并将它们联接到表中 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

Francisco Soto 12 年前

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

这项工作刚好是这项任务所需要的工作量。

Ross Smith II 11 年前

最干净的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

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

bad_at_coding 12 年前

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

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

在获胜、平局和损失表中添加触发器,只需更新汇总表中的数据。

这是一种方法。下一种方法是,如果你想要的结果不是关键任务(即不必是最新的),那么考虑添加一个计划的事件(每10或20分钟运行一次,运行上面提供的查询(我已经投了+1票)),并用它来更新汇总表。

祝你好运!