使用COALESCE和LEFT OUTER JOIN的SQL查询

Question

抱歉打扰您，但我在sqlite查询方面卡住了 :(

我有一个表:

sport country place type
ski  swe 1 1
ski  nor 2 1
ski  rus 3 1
luge swe 1 1
luge usa 2 1
luge ger 3 1
bob  nor 1 1
bob  rus 2 1
bob  ger 3 1

其中place为1表示金牌，2表示银牌，3表示铜牌。

现在正常的显示场景是一个国家列表，首先是最多的金牌，然后是银牌和铜牌。对于这个例子，它将是：

swe g:2 s:0 b:0 sum:2
rus g:0 s:1 b:1 sum:2
usa g:0 s:1 b:0 sum:1
nor g:0 s:0 b:2 sum:2

我正在做的事情是：

select
    country,
    sum(case when place = 1 then 1 else 0 end) as gold,
    sum(case when place = 2 then 1 else 0 end) as silver,
    sum(case when place = 3 then 1 else 0 end) as bronce,
    count(*) as allmedals
from 
    results 
group by 
    country 
order by 
    gold DESC, silver DESC, bronce DESC

目前为止，这是有效的。但我还有另一张表"countries"，包含全名、简称和一些"type"。

[name] [short]
usa      USA  
germany  GER  
russia   RUS  
norway   NOR  
poland   POL  

我希望将“results”和“countries”表格合并，这样我就可以从“countries”表格中列出所有表格内容，并在没有结果时显示为0。所以我尝试了以下代码：

SELECT 
    c.name as c_name,
    COALESCE(sum(case when r.place = 1 then 1 else 0 end), 0) as gold,
    COALESCE(sum(case when r.place = 2 then 1 else 0 end), 0) as silver,
    COALESCE(sum(case when r.place = 3 then 1 else 0 end), 0) as bronce
FROM 
    countries AS c 
LEFT OUTER JOIN 
    results AS r ON c.short = r.country 
WHERE 
    r.type = 1 
GROUP BY 
    r.country
ORDER BY 
    gold DESC, silver DESC, bronce DESC, c.name DESC" 

但问题是，我只看到了在“结果”表中有一行的国家列表。我认为这是因为“r.type = 1”的原因。

- user1908375

Answer 1

尝试更改

LEFT OUTER JOIN results as r ON c.short = r.country 
WHERE r.type = 1

为了

LEFT OUTER JOIN results as r ON c.short = r.country and r.type = 1

这个对我来说很好用

select 
c.name,
sum(case when place = 1 then 1 else 0 end) as gold,
sum(case when place = 2 then 1 else 0 end) as silver,
sum(case when place = 3 then 1 else 0 end) as bronce,
count(*) as allmedals
from countries  c 
left outer join results r on c.short = r.country
group by c.name
order by gold desc, silver desc, broncedesc, c.name desc

- Gratzy

---

那就是解决方案！其实我不知道在JOIN中可以执行多个操作。非常感谢你。 - user1908375

我不明白“不行啊！！！它只显示了一个结果行！”的意思。 - Gratzy

抱歉评论不清楚。例如，我有5个国家：瑞典（swe）、俄罗斯（rus）、美国（usa）、挪威（nor）和斯洛文尼亚（slo），在表“countries”中。而在“results”中只有2个：瑞典（swe）和俄罗斯（rus）。那么它会显示3行，即瑞典（swe）、俄罗斯（rus）和美国（首先按照c.name降序找到）。 - user1908375

"按组"是一个问题，但对我来说，它是"按r.country分组"。 - user1908375

@user1908375 啊，我明白了。你明白为什么那是个问题吗？你在结果表中按数据进行分组，在外部连接中，对于那些在该表中不存在的国家的所有数据都为空。因此只有一行。抱歉，我应该能够挑出来的。 - Gratzy

Answer 2

SELECT 
     c.name,
     SUM(CASE WHEN place = 1 THEN 1 ELSE 0 END) AS gold,
     sum(CASE WHEN place = 2 THEN 1 ELSE 0 END) AS silver,
     sum(CASE WHEN place = 3 THEN 1 ELSE 0 END) AS bronce,
     IFNULL (COUNT(r.country), 0) AS allmedals
FROM countries  c 
   LEFT OUTER JOIN records AS r ON c.short = r.country 
GROUP BY c.name
ORDER BY c.name asc;

- hmeftah

---

请添加更多信息。不鼓励仅有代码和“试试这个”的答案，因为它们没有可搜索的内容，并且没有解释为什么某人应该“试试这个”。 - Paritosh