mysql查询，用于为两个给定值选择相似列值的记录
mysql查询，用于为两个给定值选择相似列值的记录
提问于 2013-08-22 19:03:17
id | person | cities travelled | distance
----------+---------+-------------+-----------
1 | jack | japan | 8
2 | Joe | florida | 11
3 | Joe | california | 215
4 | Jane | ghana | 3
5 | Jane | florida | 8
6 | Joe | jakarta | 11
7 | Joe | california | 215
8 | Joe | japan | 3对于给定的两个人"jane“和"joe"，我想找出他们旅行过的共同城市。它的sql查询是什么？在这种情况下，我们可以看到简和乔都去过的共同城市是“佛罗里达”。如何借助sql查询找到它？
回答 2
Stack Overflow用户
发布于 2013-08-22 19:09:29
您可以使用group by和having实现这一点
select CitiesTravelled
from t
group by CitiesTravelled
having sum(person = 'Joe') > 0 and
sum(person = 'Jane') > 0;通过将逻辑放在having子句中，可以轻松地对查询进行泛化。以下是针对Jane或Joe的版本：
select CitiesTravelled
from t
group by CitiesTravelled
having sum(person = 'Joe') > 0 or
sum(person = 'Jane') > 0;以下是简、乔和杰克旅行过的城市的版本：
select CitiesTravelled
from t
group by CitiesTravelled
having sum(person = 'Joe') > 0 and
sum(person = 'Jane') > 0 and
sum(person = 'Jack') > 0;编辑：
如果要添加行驶距离，可以将select更改为类似以下内容：
select CitiesTravelled, min(DistanceTravelled)
select CitiesTravelled, avg(DistanceTravelled)
select CitiesTravelled, max(case when person = 'Jane' then DistanceTravelled end)Stack Overflow用户
发布于 2013-08-22 19:25:37
select CitiesTravelled
from t
where person = 'Joe'
and CitiesTravelled in (
select CitiesTravelled
from t
where person = 'Jane'
);或
select CitiesTravelled
from t t1
where person = 'Joe'
and exists (
select t2.CitiesTravelled
from t t2
where t2.person = 'Jane'
and t2.CitiesTravelled = t1.CitiesTravelled
);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18378639
复制相关文章
相似问题
腾讯云开发者
