MySQL左连接子选择
我有一个简单的语言/模板 ID 表:
I have a simple table of languages / template id's:
语言 |模板
zh, t1
恩, t1
au, t2
格，t3
恩, 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:
zh, t1, 3
zh, t2, 0
zh, 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
相关文章