MySQL 左连接子选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7520441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Left Join Subselect
提问by StuR
I have a simple table of languages / template id's:
我有一个简单的语言/模板 ID 表：
language | template
语言 | 模板
en, t1
en, t1
au, t2
ge, t3
en, t1
ge, t2
ge, t3
en, t1
en, t1
au, t2
ge, t3
en, t1
ge, t2
ge, t3
Template is always either t1,t2, or t3. In total there are 3 languages: en, au, ge.
模板始终是 t1、t2 或 t3。总共有 3 种语言：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
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，我遇到的问题是返回零计数。
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?
我在想它需要在模板 id 上进行某种左连接子选择，以确保为每种语言返回 3 个模板 id？
回答by Joshua Carmody
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:
可能有更好的方法来做到这一点，我还没有在 MySQL 中测试过，但以下在 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
回答by Charles Bretana
Select a.language, a.template, Count(*) count
From (Select Distinct language, template From table) a
Left Join table b
On b.language = a.language
And b.template = b.template
Group By a.language, a.template
回答by nathan
What you need is two tables that list the possible values for language and template.
您需要的是两个表，其中列出了语言和模板的可能值。
CREATE TABLE language (...) AS SELECT DISTINCT language FROM your_table;
CREATE TABLE template (...) AS SELECT DISTINCT template FROM your_table;
Then you can do something like this:
然后你可以做这样的事情：
SELECT l.language, t.template, SUM(CASE WHEN yours.language IS NULL THEN 0 ELSE 1 END) count
FROM language l CROSS JOIN template t
LEFT OUTER JOIN your_table yours ON l.language = yours.language AND t.template = yours.template
GROUP BY l.language, t.template;
