如何实现SQL数据表中主成员与邻居成员的对应值匹配？

Your original query runs into issues because when multiple members reference the same neighbor (like 109 and 110 both pointing to 108), the subquery in the SELECT clause tries to return multiple values for a single row—this causes an error since scalar subqueries can only return one result per row.

Instead, we can split the problem into two parts and combine the results with UNION ALL:

-- Part 1: Reverse the neighbor relationships where they exist
SELECT 
    f.Neighnour_mem AS Main_mem, 
    f.Main_mem AS Neighnour_mem
FROM family f
WHERE f.Neighnour_mem != '-'

UNION ALL

-- Part 2: Keep members with no neighbors who aren't referenced by anyone else
SELECT 
    f.Main_mem, 
    '-' AS Neighnour_mem
FROM family f
WHERE f.Neighnour_mem = '-'
AND NOT EXISTS (
    SELECT 1 
    FROM family f2 
    WHERE f2.Neighnour_mem = f.Main_mem
)

-- Sort to match your expected output
ORDER BY Main_mem;

How this works: #

• First SELECT: For every row where a member has a neighbor, we swap the Main_mem and Neighnour_mem columns. This turns entries like 103 → 102 into 102 → 103, which is what you want.
• Second SELECT: We keep members who originally had no neighbors (-) and aren't listed as a neighbor by any other member. This ensures we don't include entries like 102 (which is referenced by 103) in the null-neighbor list.
• UNION ALL: Combines both result sets without removing duplicates (which we need for cases like 108 having two neighbors: 109 and 110).
• ORDER BY: Sorts the final output to match your expected format.

Expected Output: #

Main_mem Neighnour_mem
---------------------------
101 -
102 103
104 -
105 106
107 -
108 109
108 110

Note: If your actual database uses NULL instead of - for missing neighbors, replace != '-' with IS NOT NULL and = '-' with IS NULL in the query.

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