如何在一对多关联的两张表中按位置筛选同地点用户？

Hey there! Let's figure out how to pull up users who share the same location. I'll walk you through a few straightforward methods that work across most SQL databases, plus some database-specific tweaks if you need them.

This is a tried-and-true way to match users with others in the same location. We'll join the users table to itself on the location_id, making sure we don't accidentally match a user to themselves.

SELECT DISTINCT u1.id, u1.name, l.city
FROM users u1
-- Join users table to itself where location matches but user IDs are different
JOIN users u2 ON u1.location_id = u2.location_id AND u1.id != u2.id
-- Bring in the actual city name from the location table
JOIN location l ON u1.location_id = l.id
ORDER BY l.city, u1.name;

The DISTINCT here ensures we don't get duplicate entries for the same user (since each user will pair with every other user in their location).

If you want to also see how many users are in each location, window functions make this super clear. We'll first calculate the number of users per location, then filter for locations with more than one user.

WITH user_location_summary AS (
    SELECT 
        u.id, 
        u.name, 
        l.city,
        -- Count how many users are in the same location
        COUNT(*) OVER (PARTITION BY l.id) AS total_users_in_city
    FROM users u
    JOIN location l ON u.location_id = l.id
)
-- Only keep users where their city has more than one person
SELECT id, name, city
FROM user_location_summary
WHERE total_users_in_city > 1
ORDER BY city, name;

This approach is easier to read and modify if you want to add more metrics later, like average user age or something similar.

If you just want a clean list of cities with multiple users, along with the names of those users, you can use string aggregation functions (these vary a bit by database):

MySQL/MariaDB #

SELECT l.city, GROUP_CONCAT(u.name SEPARATOR ', ') AS users_in_city
FROM users u
JOIN location l ON u.location_id = l.id
GROUP BY l.id, l.city
-- Only include cities with 2+ users
HAVING COUNT(u.id) > 1;

PostgreSQL/SQL Server #

-- PostgreSQL
SELECT l.city, STRING_AGG(u.name, ', ') AS users_in_city
FROM users u
JOIN location l ON u.location_id = l.id
GROUP BY l.id, l.city
HAVING COUNT(u.id) > 1;

-- SQL Server
SELECT l.city, STRING_AGG(u.name, ', ') AS users_in_city
FROM users u
JOIN location l ON u.location_id = l.id
GROUP BY l.id, l.city
HAVING COUNT(u.id) > 1;

All these methods should get you exactly what you need—adjust based on your database system and whether you want individual user rows or grouped city lists!

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