gpt4 book ai didi

mysql - 在 2 列上连接 mysql 表两次 = 1 列

转载 作者:行者123 更新时间:2023-11-29 14:22:19 28 4
gpt4 key购买 nike

我有一个包含消息的数据库。消息存储在一个表中,用户信息存储在另一表中。在消息表中,有一个author_id列,它代表来自用户表的作者的user_id,还有所有消息列,还有一个to_address,它代表来自用户表的“u_”+ user_id的串联。有没有什么可以连接这两个表,以便它在author_id 和to_address 中显示用户名而不是ID。

我已经尝试过

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

有明显错误我尝试过使用子查询,例如

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 行”。有什么办法可以成功做到这一点吗?

最佳答案

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

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 表的特定实例中选择特定列。

关于mysql - 在 2 列上连接 mysql 表两次 = 1 列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11567815/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com