在 2 列 = 1 列上加入 mysql 表两次 [英] Join mysql tables twice on 2 columns = 1 column

问题描述

我有一个包含消息的数据库.消息存储在一个表中，用户信息存储在另一个表中.在message表中，有一个author_id列，代表user表中作者的user_id，有所有message列，还有一个to_address，代表user表中u_"+user_id的串联.有没有什么我可以加入这两个表，以便它在 author_id 和 to_address 中显示用户名而不是 ID.

I have a database that contains messages. The messages are stored in one table, the user information is stored in another. In the message table, there is an author_id column which represents the user_id of the author from the user table, there are all the message columns, and there is a to_address which represents a concatenation of "u_" + user_id from the user table. Is there any that I can join these two tables, so that it display the username instead of ID in BOTH the author_id AND to_address.

我试过了

SELECT  username, ..., username
  FROM msgs
    INNER JOIN users
      ON user_id=author_id AND concat("u_",user_id)=to_address;

有明显错误我试过使用诸如

with obvious error I've tried using subqueries such as

SELECT
  ( SELECT username 
      FROM users
        INNER JOIN msgs
          ON user_id=author_id
  ) AS "From",
  ( SELECT username
      FROM users
        INNER JOIN msgs
          ON CONCAT("u_",user_id)=to_address
  ) AS "To",
  ( SELECT timestamp(message_time) FROM msgs
  ) AS "Sent",
  ( SELECT message_subject FROM msgs
  ) AS "Subject",
  ( SELECT message_text AS "Message" FROM msgs
  ) AS "Message"

并得到子查询返回多于 1 行".有什么办法可以成功地做到这一点吗?

and got "Subquery returns more than 1 row". Is there any way that I can do this successfully?

推荐答案

听起来你想要这样的东西:

It sounds like you want something like this:

SELECT
    from_user.username AS "From",
    to_user.username AS "To",
    timestamp(msgs.message_time) AS "Sent",
    msgs.message_subject AS "Subject",
    msgs.message_text AS "Message"

FROM msgs

INNER JOIN users AS from_user
ON msgs.author_id = from_user.user_id

INNER JOIN users AS to_user
ON msgs.to_address = CONCAT("u_", to_user.user_id);

基本上，您将 users 表连接到 msgs 表两次，为表的每个实例提供不同的名称和不同的连接条件.然后，您可以从 users 表的特定实例中选择特定列.

Basically, you join the users table to the msgs table twice, giving each instance of the table a different name and a different join condition. Then you can pick a specific column out of a specific instance of the users table.

这篇关于在 2 列 = 1 列上加入 mysql 表两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！