求SQL语句：筛选同时拥有Noida和Chennai地址的客户全量行

Hey there, let's tackle this SQL problem together!

First, let's lay out the sample data we're working with:

Sample Input #

What We Need #

We want to fetch all Noida/Chennai rows for customers who have addresses in both cities—matching the expected output you shared.

The Solution Query #

Here's a clean, efficient way to do this using a subquery to first identify qualifying customers, then pull their relevant rows:

SELECT t.*
FROM your_table_name t
INNER JOIN (
    -- Step 1: Find customers who have both Noida and Chennai addresses
    SELECT Customer
    FROM your_table_name
    WHERE City IN ('Noida', 'Chennai')
    GROUP BY Customer
    HAVING COUNT(DISTINCT City) = 2
) qualifying_customers 
    ON t.Customer = qualifying_customers.Customer
-- Step 2: Filter to only the Noida/Chennai rows for these customers
WHERE t.City IN ('Noida', 'Chennai');

How It Works #

1. Subquery: The inner query narrows down to customers who have entries for both cities. We filter rows to only Noida/Chennai first, group by customer, and use HAVING COUNT(DISTINCT City) = 2 to ensure they have both (this avoids counting duplicates if a customer has multiple entries for the same city).
2. Join & Filter: We join this list of qualifying customers back to the original table, then add a final filter to only keep the Noida and Chennai rows. If you wanted all rows for these customers—including Test2's Bengaluru entry—just remove the last WHERE clause.

Expected Output #

Running this query will give you exactly what you're looking for:

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