英文:
select records with two different values
问题
使用Oracle SQL，如果我有这个数据集，如何只选择那些同时具有欧洲和美洲值的记录？换句话说，我不想在报表中看到Johnny Bravo。
| 姓名 | 姓氏 | 大陆 |
|---|---|---|
| Johnny | Bravo | 美洲 |
| Pier | Ruso | 欧洲 |
| Pier | Ruso | 美洲 |
谢谢
英文:
Using Oracle SQL, If I have this dataset, how can I select only those records which has value of both Europe and America? In other words I don't wanna see Johnny Bravo on the report.
| Name | Surname | Continent |
|: --- |: ------ |: -------- |
|Johnny| Bravo | America |
|Pier | Ruso | Europe |
|Pier | Ruso | America |
Thank you
答案1
得分: 0
这是一个选项；它期望每个姓名有两个不同的大陆，其中一个是美洲，另一个是欧洲。
Johnny没有返回，因为他只有一个大陆（美洲），而Mike也不在结果集中，因为他有3个大陆（其中两个是美洲和欧洲，但该人在表格中有3个大陆）。
示例数据：
SQL> with test (name, surname, continent) as
2 (select 'Johnny', 'Bravo', 'America' from dual union all
3 --
4 select 'Pier' , 'Ruso' , 'Europe' from dual union all
5 select 'Pier' , 'Ruso' , 'America' from dual union all
6 --
7 select 'Mike' , 'Tiger', 'Europe' from dual union all
8 select 'Mike' , 'Tiger', 'Asia' from dual union all
9 select 'Mike' , 'Tiger', 'America' from dual
10 )
查询：
11 select name, surname
12 from test
13 group by name, surname
14 having count(distinct continent) = 2
15 and min(continent) = 'America'
16 and max(continent) = 'Europe';
NAME SURNAME
Pier Ruso
SQL>
英文:
Here's one option; it expects that there are two distinct continents per name, where one of them is America and another Europe.
Johnny isn't returned as he has only one continent (America), and Mike isn't in result set as he has 3 continents (two of them are America and Europe, but - there are 3 continents in a table for that person):
Sample data:
SQL> with test (name, surname, continent) as
2 (select 'Johnny', 'Bravo', 'America' from dual union all
3 --
4 select 'Pier' , 'Ruso' , 'Europe' from dual union all
5 select 'Pier' , 'Ruso' , 'America' from dual union all
6 --
7 select 'Mike' , 'Tiger', 'Europe' from dual union all
8 select 'Mike' , 'Tiger', 'Asia' from dual union all
9 select 'Mike' , 'Tiger', 'America' from dual
10 )
Query:
11 select name, surname
12 from test
13 group by name, surname
14 having count(distinct continent) = 2
15 and min(continent) = 'America'
16 and max(continent) = 'Europe';
NAME SURNAME
------ ----------
Pier Ruso
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论