MySQL Left Join子选择 [英] MySQL Left Join Subselect

查看:106
本文介绍了MySQL Left Join子选择的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个简单的语言表/模板ID:

I have a simple table of languages / template id's:

语言|模板

en,t1
zh_CN,t1
au,t2
ge,t3
zh_CN,t1
ge,t2
ge,t3

en, t1
en, t1
au, t2
ge, t3
en, t1
ge, t2
ge, t3

模板始终为t1,t2或t3.总共有3种语言:en,au,ge.

Template is always either t1,t2, or t3. In total there are 3 languages: en, au, ge.

表中还有很多信息,我仅显示与此问题相关的信息,我将使用数据进行图形绘制,因此需要以以下格式返回:

There is lots more information in the table, I am just showing what is relevant to this question, I will be using the data for graphing and so need it returning in this format:

en,t1,3
en,t2,0
en,t3,0
au,t1,0
au,t2,1
au,t3,0
ge,t1,0
ge,t2、1
ge,t3、2

en, t1, 3
en, t2, 0
en, t3, 0
au, t1, 0
au, t2, 1
au, t3, 0
ge, t1, 0
ge, t2, 1
ge, t3, 2

这将计算每种语言中出现的模板数量.但是,如果表中没有该特定语言的模板ID,那么我的问题是返回零计数.

This counts however many template occurrences there are in each language. But, the problem I have is returning a zero count if there are no template id's for that particular language in the table.

我当时想需要在模板ID上进行某种左联接子选择,以确保每种语言都返回3个模板ID?

I was thinking it would need some sort of left join sub select on the template id to make sure the 3 template id's are returned for each language?

推荐答案

也许有更好的方法,我还没有在MySQL中对其进行测试,但是以下在SQL Server 2005中起作用:

There might be a better way of doing this, and I haven't tested it in MySQL, but the following works in SQL Server 2005:

Select a.language, b.template, count (c.template) as combo_count
from
(select distinct language from tablename) as a
inner join (select distinct template from tablename) as b on 1 < 2 /* this could be cross join, same thing. */
left outer join tablename c on c.language = a.language and c.template = b.template
group by a.language, b.template
order by 1, 2

以下是带有示例数据的结果:

Here are the results with your sample data:

au  t1  0
au  t2  1
au  t3  0
en  t1  3
en  t2  0
en  t3  0
ge  t1  0
ge  t2  1
ge  t3  2

这篇关于MySQL Left Join子选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆