不喝点酒怎么样 union ?

SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
             ELSE m.user_to_id
        END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
ORDER BY m.send_date DESC;

这可能不是您真正想要的，因为用户将在列表中出现多次。你真正想要的是：

SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
             ELSE m.user_to_id
        END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
GROUP BY m.user_id
ORDER BY MAX(m.send_date) DESC;

with x as(
SELECT user_from_id as user_id, send_date
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id, send_date
FROM message
WHERE user_from_id=xxx
) select user_id 
from x 
order by send_date

这包裹了 UNION 向上查询 send_date 列转换为单个变量，然后可以从中 SELECT 以及 ORDER BY send_date .