MySQL新手求助：合并同表两行并优化DISTINCT查询输出

Hey there! Let's tackle your two MySQL questions one by one, starting with the specific query issue you're facing since you provided sample data.

Your current query returns pairs of IDs where 213 is either the sender or receiver, but you want just the unique associated ID in a single column. Here are two straightforward solutions:

Method 1: Use a CASE statement to pick the non-213 ID #

This checks each row and selects the ID that isn't 213, then uses DISTINCT to ensure uniqueness:

SELECT DISTINCT 
    CASE 
        WHEN uid != 213 THEN uid 
        ELSE r_uid 
    END AS `new id`
FROM messenger
WHERE uid = 213 OR r_uid = 213;

Method 2: Combine results with UNION and filter out 213 #

This approach first fetches all receiver IDs where 213 is the sender, then all sender IDs where 213 is the receiver, combines them, removes duplicates, and excludes 213 itself:

SELECT DISTINCT id AS `new id`
FROM (
    SELECT r_uid AS id FROM messenger WHERE uid = 213
    UNION ALL
    SELECT uid AS id FROM messenger WHERE r_uid = 213
) AS temp_table
WHERE id != 213;

Both will give you the desired output:

new id
-----------
239

Merging rows depends on what exactly you need to combine—here are two common scenarios with solutions:

Scenario 1: Merge rows with shared key (e.g., same user ID) to fill missing values #

If you have two rows for the same entity (like a user) with complementary data, use GROUP BY with aggregate functions to combine them:
Suppose your table user_data looks like this:

id  | name  | phone   | email
----|-------|---------|-----------
213 | Alice | 555-123 | NULL
213 | Alice | NULL    | alice@x.com

Merge into one row:

SELECT 
    id,
    MAX(name) AS name,
    MAX(phone) AS phone,
    MAX(email) AS email
FROM user_data
WHERE id = 213
GROUP BY id;

This will pick the non-null value for each column.

Scenario 2: Concatenate values from multiple rows into a single field #

If you want to combine text values from multiple rows (like notes or tags), use GROUP_CONCAT:
Suppose your table user_notes looks like this:

user_id | note
--------|-------------------
213     | Meeting at 3 PM
213     | Bring project docs

Merge the notes into one field:

SELECT 
    user_id,
    GROUP_CONCAT(note SEPARATOR ' ') AS combined_notes
FROM user_notes
WHERE user_id = 213
GROUP BY user_id;

You can adjust the SEPARATOR to use commas, newlines, or whatever fits your needs.

If your merge scenario is different (like row-to-column pivoting), feel free to share more details about your table structure and desired output!

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