请帮我设计一个sql查询来解决这个问题

Question

对于特定名称，我想获取居住在三个或更多城市的其他名称被这个人住过。

Answer 1

这就是你应该做的：

重新设计你的数据库以拥有一个城市表：

city(id int, name varchar)

和一个用户表：

user(id int, name varchar, ...)

以及一个 user_city 表：

user_city(user_id int, city_id int)

仅此一项就可以消除每个用户 10 个城市的限制。

查找用户居住的城市：

select city_id form user_city where user_id = ?

现在如何从该列表中查找居住在 3 个或更多城市的用户？
一种方法是从每个用户居住的列表中计算城市的数量，例如：

select user_id,count(*) n 
from user_city 
where city_id in (select city_id 
                  from user_city 
                  where user_id = ?) 
group by user_id having n >= 3;

我没有真正测试过这个，但它应该可以工作。

您还必须弄清楚如何为这些表建立索引。

Answer 2

您需要使用binomial(10,3)^2 OR 条件来执行查询。那是 14 400。你不想这样做。

Answer 3

您需要重新设计您的表，而不是

name 、 city1 、 city2 、 city3 、city4 、 city5 、city6 、 city7 、 city8 、 city9 city10 ，

它应该更像

Person, City, rank
------------------
name , city1 ,1
name , city2 ,2 
name , city3 ,3 
name , city4 ,4 
name , city5 ,5 
name , city6 ,6 
name , city7 ,7 
name , city8 ,8 
name , city9 ,9 
name , city10,10

是接受 TomTom 的建议并了解数据规范化！

Answer 4

尊重您不重新设计数据库的请求

我未经尝试的想法，现在无法测试

通过联合 select name, c1 创建视图（名称，城市）、选择名称、c2 等...

然后：

select m2.name from myview m1
inner join myview m2 on m1.city = m2.city
where m1.name = @Name AND m2.Name!=@Name
group by m2.name
having count(m2.name) > 2 

Answer 5

是的。

您可以将表格发送回设计者，并附上评论以了解如何设计表格。第一范式，规范化。

一旦表遵循 SQL 规则，查询就非常容易。

Answer 6

尝试这样的操作：

SELECT PersonName,COUNT(*) AS CountOf
    FROM (SELECT PersonName,city1 FROM PersonCities WHERE city1 IS NOT NULL
          UNION SELECT PersonName,city2 FROM PersonCities WHERE city2 IS NOT NULL
          UNION SELECT PersonName,city3 FROM PersonCities WHERE city3 IS NOT NULL
          UNION SELECT PersonName,city4 FROM PersonCities WHERE city4 IS NOT NULL
          UNION SELECT PersonName,city5 FROM PersonCities WHERE city5 IS NOT NULL
          ...
         ) dt
    WHERE dt.city1 IN (SELECT city1 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city1 IS NOT NULL
                       UNION SELECT city2 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city2 IS NOT NULL
                       UNION SELECT city3 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city3 IS NOT NULL
                       UNION SELECT city4 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city4 IS NOT NULL
                       UNION SELECT city5 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city5 IS NOT NULL
                       ...
                       )
        AND PersonName!=@SearchPerson
    GROUP BY PersonName
    HAVING COUNT(*)>=3

我没有 mysql，所以这里使用 SQL Server 运行：

DECLARE @PersonCities table(PersonName varchar(10), city1 varchar(10), city2 varchar(10), city3 varchar(10), city4 varchar(10), city5 varchar(10))
INSERT INTO @PersonCities VALUES ('Joe','AAA','BBB','CCC', NULL, NULL)
INSERT INTO @PersonCities VALUES ('Pat','BBB','DDD','EEE','FFF','GGG')
INSERT INTO @PersonCities VALUES ('Sam','FFF','BBB', NULL, NULL, NULL)
INSERT INTO @PersonCities VALUES ('Ron','HHH','DDD','EEE','FFF', NULL)
INSERT INTO @PersonCities VALUES ('Don','FFF','ZZZ','QQQ', NULL, NULL)

DECLARE @SearchPerson varchar(10)
SET @SearchPerson='Pat'

SELECT PersonName,COUNT(*) AS CountOf
    FROM (SELECT PersonName,city1 FROM @PersonCities WHERE city1 IS NOT NULL
          UNION SELECT PersonName,city2 FROM @PersonCities WHERE city2 IS NOT NULL
          UNION SELECT PersonName,city3 FROM @PersonCities WHERE city3 IS NOT NULL
          UNION SELECT PersonName,city4 FROM @PersonCities WHERE city4 IS NOT NULL
          UNION SELECT PersonName,city5 FROM @PersonCities WHERE city5 IS NOT NULL
         ) dt
    WHERE dt.city1 IN (SELECT city1 FROM @PersonCities WHERE PersonName=@SearchPerson AND city1 IS NOT NULL
                       UNION SELECT city2 FROM @PersonCities WHERE PersonName=@SearchPerson AND city2 IS NOT NULL
                       UNION SELECT city3 FROM @PersonCities WHERE PersonName=@SearchPerson AND city3 IS NOT NULL
                       UNION SELECT city4 FROM @PersonCities WHERE PersonName=@SearchPerson AND city4 IS NOT NULL
                       UNION SELECT city5 FROM @PersonCities WHERE PersonName=@SearchPerson AND city5 IS NOT NULL
                       )
        AND PersonName!=@SearchPerson
    GROUP BY PersonName
    HAVING COUNT(*)>=3

输出：

PersonName 
---------- -----------
Ron        3

(1 row(s) affected)

Answer 7

您需要标准化您的数据库。

执行此操作，您将获得

“名称”、“城市”列（可选的“城市顺序”）。

之后，您需要找到一种方法将这些结果组合成您需要的结果。为此，您需要了解连接、计数和分组依据。

Answer 8

试试这个：

<表>人
<字段>人员 ID、人员名称 |

<表>城市
<字段>城市 ID、城市名称 |

<表>曾住过
<字段> LivedInId、PersonId、CityId

从逻辑上讲，您将为每个场景执行以下操作：

1. 查找居住在最大数量的不同城市的人：
   制作 PersonId 的列表（所有人）
   迭代并计算每个人居住的城市数量
   查找有人居住的最多城市
   查找与拥有最多城市的 personId 相关的人名

2. 查找居住在 3 个或更多城市的所有人员作为给定人员
   我们称这个人为鲍勃
   列出 Bob 居住过的所有城市（CityId）。
   制作一个包含 personId 和常见城市的列表（可能是 Java 中的 HashMap）
   迭代 LivedIn 表并更新常见城市的数量
   查找计数大于 3 的所有人员

我会结合使用 Java 和 SQL 来完成此操作，但我对这两者都不太擅长，因此无法在此处为您提供代码，而不必查看很多东西向上。

Answer 9

将此数据分为三个表以提供更灵活的多对多关系。

person 用于存储姓名的表
用于存储城市的city表
person_city 将两者关联起来（多对多）

要检索曾居住在 navin 拥有的 3 个或更多城市的其他人：

SELECT name FROM (
    SELECT
        p.name, COUNT(DISTINCT(city_id)) AS lived
    FROM person p 
    JOIN person_city pc ON (pc.person_id = p.person_id) 
    JOIN city c ON (c.city_id = pc.city_id) 
    WHERE city_id IN (
        SELECT c2.city_id 
        FROM city c2 
        JOIN person_city pc2 ON (c2.city_id = pc2.city_id) 
        JOIN person p2 ON (p2.person_id = pc2.person_id) 
        WHERE p2.name = 'navin' 
    )
    GROUP BY person_id HAVING lived >= 3
) AS multihome 
WHERE name <> 'navin';