使用mysql在一个查询中在多个表中选择总数 [英] Select total of childrens in multiple tables in one query with 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:
用户表
id_user | name
1 | foo
2 | bar
wons表
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
我试图得到这样的东西:
I'm trying to get something like this:
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;
推荐答案
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.
这篇关于使用mysql在一个查询中在多个表中选择总数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文