抱歉打扰您,但我在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”的原因。