SQL查询需求：获取多城市用户ID及去重关联城市值

Got it, let's break down how to write this SQL query properly. The goal is to pull user IDs that have more than one distinct city associated with them, plus the unique city values for each qualifying user.

Assumed Table Structure #

First, let's assume we have a table (let's call it user_cities) with two key columns:

• user_id: The ID of the user
• city: The city linked to the user (could have multiple entries per user, possibly duplicates)

If your data is split across multiple tables (like a users table and a user_addresses table), I'll cover that scenario too.

Method 1: Use a CTE to Filter Qualifying Users First #

This approach first identifies users with multiple distinct cities, then joins back to get their unique cities:

-- Step 1: CTE to get users with >1 distinct city
WITH users_with_multiple_cities AS (
    SELECT user_id
    FROM user_cities
    GROUP BY user_id
    HAVING COUNT(DISTINCT city) > 1
)
-- Step 2: Get unique cities for those users
SELECT 
    uc.user_id,
    uc.city
FROM user_cities uc
JOIN users_with_multiple_cities uwmc 
    ON uc.user_id = uwmc.user_id
GROUP BY uc.user_id, uc.city  -- Ensure each city per user is unique
ORDER BY uc.user_id, uc.city;

How This Works: #

• The CTE users_with_multiple_cities groups records by user_id and uses COUNT(DISTINCT city) to count unique cities per user. We keep only users where this count is greater than 1.
• The main query joins this CTE back to the original table to fetch all cities for those users, then groups by user_id and city to eliminate duplicate city entries for the same user.

Method 2: Use Window Functions for a More Concise Query #

If you prefer using window functions (great for avoiding explicit joins), here's another way:

SELECT DISTINCT
    user_id,
    city
FROM (
    SELECT 
        user_id,
        city,
        -- Calculate total distinct cities per user for each row
        COUNT(DISTINCT city) OVER (PARTITION BY user_id) AS distinct_city_count
    FROM user_cities
) subquery
-- Filter only users with >1 distinct city
WHERE distinct_city_count > 1
ORDER BY user_id, city;

How This Works: #

• The subquery adds a column distinct_city_count that shows the total number of unique cities for the user, using PARTITION BY user_id to calculate this per user.
• The outer query filters for users where this count is greater than 1, then uses DISTINCT to ensure each city per user is only listed once.

For Split Tables (Users + Addresses) #

If your user data is in a users table and city data is in a user_addresses table (with user_id as the foreign key), adjust the query like this:

WITH users_with_multiple_cities AS (
    SELECT ua.user_id
    FROM user_addresses ua
    GROUP BY ua.user_id
    HAVING COUNT(DISTINCT ua.city) > 1
)
SELECT 
    u.user_id,
    ua.city
FROM users u
JOIN user_addresses ua 
    ON u.user_id = ua.user_id
JOIN users_with_multiple_cities uwmc 
    ON u.user_id = uwmc.user_id
GROUP BY u.user_id, ua.city
ORDER BY u.user_id, ua.city;

Edge Case Note: Handling NULL Cities #

If your city column can have NULL values, COUNT(DISTINCT city) will ignore them by default. If you want to treat NULL as a valid "city" for counting, use COALESCE to replace NULL with a placeholder:

HAVING COUNT(DISTINCT COALESCE(city, 'NULL_CITY')) > 1

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