MariaDB中如何查询指定用户收发消息并关联显示用户名

你现在有两张关联表，结构如下：

-- 表 USERS
ID    Name
100   User1
243   User2
681   User3

-- 表 MSGS
FromU  ToUser  Txt          DTime
243    100     Bla bla bla  [...]
100    243     Taaa         [...]
243    681     Bo bo bo     [...]
681    243     Mi mi miiii  [...]
681    100     Exemples     [...]
100    681     Very good    [...]

关联关系是：MSGS.FromU 关联 USERS.ID，MSGS.ToUser 关联 USERS.ID。

你的需求是：查询某一用户（比如User2，ID=243）的所有发送、收到的消息，同时显示收发双方的用户名，最终结果要包含发送方ID、发送方名称、接收方ID、接收方名称、消息内容，还要按时间排序。

现在你用的SQL语句只能拿到ID和消息内容，没法获取用户名：

SELECT FromU, ToUser, Txt FROM msgs WHERE FromU = 243 OR ToUser = 243 ORDER BY DTime ASC

要同时获取收发双方的用户名，你需要两次关联USERS表，分别给它们起不同的别名来区分发送方和接收方的用户信息。具体SQL如下：

SELECT 
    m.FromU AS `FromU ID`,
    fu.Name AS `FromU Name`,
    m.ToUser AS `ToUser ID`,
    tu.Name AS `ToUser Name`,
    m.Txt,
    m.DTime
FROM msgs m
JOIN users fu ON m.FromU = fu.ID
JOIN users tu ON m.ToUser = tu.ID
WHERE m.FromU = 243 OR m.ToUser = 243
ORDER BY m.DTime ASC;

语句解释： #

• 给msgs表起别名m，简化代码书写；
• 第一次关联users表并起别名fu（from user），用来匹配发送方的ID和用户名；
• 第二次关联users表并起别名tu（to user），用来匹配接收方的ID和用户名；
• 使用AS给列起更清晰的别名（比如FromU ID），方便阅读结果；
• 保留ORDER BY DTime ASC来按消息时间顺序展示。

执行这条语句后，就能得到你想要的包含收发双方用户名的完整结果啦～

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