sql - 如何在包含文本的列上连接表格

Question

我有 2 个表要合并：

t1

Continent    Country     City
-----------------------
Europe      Germany      Munich

NA          Canada       Ontario

Asia        Singapore    (blank)

Asia        Japan        Tokyo

和

t2

Country      Status
-----------------
Germany      Complete

Canada       Incomplete

Singapore    Complete

Japan        Complete

我想获得第二高“完成”状态的大陆。我是 SQL 新手，我正在努力学习基础知识，但我无法完成这项工作。

标签： sql

Answer 1

我理解你的意思是拥有更多城市状态完整的国家。您可以使用子查询：

with a as
(
select a.country,
sum(case when status = 'Complete' then 1 else 0 end) as CompleteCount 
from t1 a inner join t2 b on a.country = b.country
group by a.country
)

select country from
(
select country, 
ROW_NUMBER() OVER( ORDER BY CompleteCount  desc) as OrderComplete
from a
)a where OrderComplete = 2