含DISTINCT COUNT关联子查询的SQL无结果问题排查

Let's walk through the problems with your current query and fix them to get the expected results.

1. Critical Syntax Errors in Your Subquery #

Your original query has two syntax mistakes that prevent it from executing correctly:

• You're missing the FROM keyword in the subquery (you wrote Customer i instead of FROM Customer i)
• The parenthesis placement is incorrect, leaving the subquery unclosed properly

Here's what your broken subquery looks like:

(SELECT COUNT(DISTINCT customer_id) Customer i WHERE o.salesman_id = i.salesman_id)

And here's the corrected version:

(SELECT COUNT(DISTINCT customer_id) FROM Customer i WHERE o.salesman_id = i.salesman_id)

2. Logical Issue: Unwanted Duplicates & Mismatched Output Fields #

Your query uses SELECT *, which would return all columns from the Customer table—including duplicate entries for the same salesman (since they have multiple customers). Your expected result only needs salesman_id and city, so you should adjust the selected fields and add DISTINCT to avoid duplicates.

Fixed Correlated Subquery Version #

Here's the corrected full query that will return the expected salesman IDs and their customers' cities:

SELECT DISTINCT o.salesman_id, o.city
FROM Customer o 
WHERE 2 <= (SELECT COUNT(DISTINCT customer_id) FROM Customer i WHERE o.salesman_id = i.salesman_id);

A More Efficient Alternative: Using GROUP BY + HAVING #

Instead of a correlated subquery, a cleaner and more efficient approach is to group the data by salesman_id and filter groups with 2+ customers using HAVING:

SELECT salesman_id, MIN(city) AS city
FROM Customer
GROUP BY salesman_id
HAVING COUNT(DISTINCT customer_id) >= 2;

Note: We use MIN(city) here because salesman 5001's customers are both in New York, and salesman 5002's customers are in California and London. Your expected result lists "Paris" for salesman 5002, which doesn't match the sample data—this is likely a typo, as none of 5002's customers are based in Paris.

Why Your Original Query Returned No Results #

The syntax errors in your subquery caused the database to fail parsing the query entirely, so it couldn't return any results at all. Fixing those syntax issues alone would make the query return data, and adjusting the selected fields would align it with your expected output.

内容的提问来源于stack exchange，提问作者Rookie_123