在SQL中编写子查询，如何组合两个查询?

Question

我的目标是找到排名前10的城市中的前10个国家。我成功地使用了这个查询:

SELECT 
COUNT(A.customer_id) AS number_of_customers,
D.country, C.city
FROM
customer A
INNER JOIN 
address B ON A.address_id = B.address_id
INNER JOIN 
city C ON B.city_id = C.city_id
INNER JOIN 
country D ON C.country_ID = D.country_ID
WHERE 
country IN ('India', 'China', 'United States', 'Japan', 'Mexico', 'Brazil', 'Russian Federation', 'Phillipines', 'Turkey', 'Indonesia')
GROUP BY 
C.city, D.country
ORDER BY 
number_of_customers DESC
LIMIT 10

但是我想使用子查询而不是列出我使用前一个查询找到的国家:

SELECT 
COUNT(A.customer_id) AS number_of_customers,
D.country
FROM
customer A
INNER JOIN 
address B ON A.address_id = B.address_id
INNER JOIN 
city C ON B.city_id = C.city_id
INNER JOIN 
country D ON C.country_ID = D.country_ID
GROUP BY 
D.country
ORDER BY 
number_of_customers DESC
LIMIT 10

如何正确地组合这两个查询?当我试图在我发布的第二个查询中替换国家列表时，我不断得到不同的错误。如果这是个愚蠢的问题，我很抱歉;我是初学者。

我的尝试:

SELECT 
COUNT(A.customer_id) AS number_of_customers,
D.country, C.city
FROM
customer A
INNER JOIN 
address B ON A.address_id = B.address_id
INNER JOIN 
city C ON B.city_id = C.city_id
INNER JOIN 
country D ON C.country_ID = D.country_ID
WHERE 
country IN (SELECT COUNT(A.customer_id) AS number_of_customers, D.country
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_ID = D.country_ID
GROUP BY D.country
ORDER BY number_of_customers DESC
LIMIT 10)
GROUP BY 
C.city, D.country
ORDER BY 
number_of_customers DESC
LIMIT 10

但是我得到一个错误

子查询有太多的列

Answer 1

例如，在PostgreSQL中，您可以使用with查询，参见文档:

WITH top_countries AS (
SELECT count(A.customer_id) AS number_of_customers,
D.country AS country
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_ID = D.country_ID
GROUP BY D.country
ORDER BY number_of_customers DESC
LIMIT 10
)
SELECT count(A.customer_id) AS number_of_customers,
D.country, C.city
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_ID = D.country_ID
WHERE country IN (SELECT tc.country FROM top_countries)
GROUP BY C.city,D.country
ORDER BY number_of_customers DESC
LIMIT 10