在SQL中，当可能有两个不同的观察结果时，如何选择唯一的用户id

Question

我有一个包含人名和国籍的表。一个人可能有双重国籍，但如果他们有美国国籍，我只想为这个人争吵。

例如：\

Name   Citizenship
John       US
John      England
Jim       Germany
Mark      US
Mark      Belgium

预期输出

Name    Country
John     US
Jim      Germany
Mark     US

提前感谢你的帮助。

Answer 1

您可以使用ROW_NUMBER()窗口函数，如：

select *
from (
select name, citizenship,
row_number() over(partition by name 
order by case when citizenship = 'US' then 1 else 2 end, citizenship
) as rn
from t 
) x
where rn = 1

Answer 2

使用NOT EXISTS:

select t.* from tablename t
where t.citizenship = 'US'
or not exists (select 1 from tablename where name = t.name and citizenship = 'US')

或具有FIRST_VALUE()窗口功能：

select distinct name,
first_value(citizenship) 
over (partition by name order by case when citizenship = 'US' then 1 else 2 end) citizenship
from tablename

请参阅演示
结果：

> Name | Citizenship
> :--- | :----------
> John | US         
> Jim  | Germany    
> Mark | US  

Answer 3

这里有一种口味：

SELECT DISTINCT
Name,
Citizenship= CASE WHEN COUNT(CASE WHEN Citizenship = 'US' THEN 1 ELSE 0 END) OVER (PARTITION BY Name) > 1 THEN 'US' ELSE Citizenship END
FROM 
t