如何从两个表中查询多个条件？

Question

我想根据这些条件从世界城市中挑选城市：

• 这个城市的人口超过了菲律宾人口最多的城市。例如，X市是菲律宾人口最多的城市。我们要列出人口超过X城市的城市，
• 在美国有一个城市(美国城市)，其城市名称与选定的城市

相同

worldcity.colomns = ['city', 'lat','lng','country','iso2','iso3','capital','population','id']

uscity.columns = ['city', 'state_id', 'state_name', 'county_fips', 'county_name',
   'lat', 'lng', 'population', 'density', 'source', 'military',
   'incorporated', 'timezone', 'ranking', 'zips', 'id']

query = """
SELECT DISTINCT
    city
FROM
    worldcity as w
inner join
      uscity as u
on
      w.city = u.city
WHERE w.population >= (MAX)population IN
    (SELECT
        population
    FROM worldcity
    WHERE
        country = 'Filipina';
    )
"""
sql_run(query)

OperationalError:接近“填充”：语法错误

Answer 1

调整WHERE子句以将MAX放置在子查询中：

...
WHERE w.population > (
    SELECT MAX(population)
    FROM worldcity 
    WHERE country = 'Filipina'
)

Answer 2

我稍微改变了你写的答案

也许这会有帮助，因为我试过了，而且成功了

如果你想把菲律宾人口最多的城市包括进来，你可以改变(> >=)

query = """
SELECT DISTINCT w.city
FROM 
      worldcity as w 
inner join
      uscity as u
on 
      w.city = u.city
WHERE w.population > (SELECT MAX(population)
                      FROM worldcity 
                      WHERE country = 'Philippines')
ORDER BY w.city
"""
sql_run(query)