mysql - 加入和更新视图 SQL
问题描述
我创建了一个视图并想要修改它。此视图允许您检索有关我正在开发的聊天的信息。
在我看来,我通过发送者“from_id”的标识符和接收者“to_id”的标识符来恢复消息。我还想在“from_username”字段中添加发送者的用户名,在“to_username”字段中添加接收者的用户名。
不幸的是,我没有看到任何解决方案,因为我根本不知道这种 SQL 语法。
select
`cekomecrm`.`chat`.`id` AS `chat_id`,
`cekomecrm`.`chat`.`date` AS `chat_date`,
`cekomecrm`.`chat`.`message` AS `chat_message`,
`cekomecrm`.`chat`.`attachment` AS `chat_attachment`,
`cekomecrm`.`chat`.`etat` AS `chat_etat`,
`cekomecrm`.`chat`.`reply_to` AS `reply_to`,
`cekomecrm`.`chat_from`.`from_id` AS `from_id`,
`cekomecrm`.`chat_from`.`type` AS `from_type`,
`cekomecrm`.`chat_to`.`to_id` AS `to_id`,
`cekomecrm`.`chat_to`.`type` AS `to_type`,
`cekomecrm`.`chat_projet`.`projet_id` AS `projet_id`,
`cekomecrm`.`chat_projet`.`task_id` AS `task_id`,
`cekomecrm`.`users`.`username` AS `to_username`,
`cekomecrm`.`users`.`username` AS `from_username`
from
(
(
(
`cekomecrm`.`chat`
join `cekomecrm`.`chat_from` on(
(
`cekomecrm`.`chat`.`id` = `cekomecrm`.`chat_from`.`chat_id`
)
)
)
join `cekomecrm`.`chat_to` on(
(
`cekomecrm`.`chat`.`id` = `cekomecrm`.`chat_to`.`chat_id`
)
)
)
join `cekomecrm`.`chat_projet` on(
(
`cekomecrm`.`chat`.`id` = `cekomecrm`.`chat_projet`.`chat_id`
)
)
join `cekomecrm`.`users` on(
(
`cekomecrm`.`chat_from`.`from_id` = `cekomecrm`.`users`.`id` AND
`cekomecrm`.`users`.`to_username` = `cekomecrm`.`users`.`username`
)
)
join `cekomecrm`.`users` on(
(
`cekomecrm`.`chat_to`.`to_id` = `cekomecrm`.`users`.`id` AND
`cekomecrm`.`users`.`from_username` = `cekomecrm`.`users`.`username`
)
)
)
这是我的观点的截图。
我觉得奇怪的是,当我创建视图时,我绝对没有使用这种语法。我想知道它是如何工作的,以及是否可以做我想做的事。
解决方案
我终于通过重构我的观点解决了我的问题。
在我的例子中,我需要通过使用他们的 id 来区分发送消息的用户和接收消息的用户。
为此,我不得不加入。我遇到的问题是我有一个带有用户名字段的用户表,并且要使用同一个字段进行多个连接,我必须用别名来区分它。
所以看起来像这样。
FROM chat
INNER JOIN users AS uto ON uto.id = chat_to.to_id
INNER JOIN users AS ufrom ON ufrom.id = chat_from.from_id
现在我已经区分了两个具有相同字段的连接(这里是我的用户表的 id),我用他们的别名选择它就足够了。
SELECT
uto.username AS to_username,
ufrom.username AS from_username
所以我的完整视图看起来像这样。
CREATE VIEW ViewProjet_chat AS
SELECT
chat.id AS chat_id,
chat.date AS chat_date,
chat.message AS chat_message,
chat.attachment AS chat_attachment,
chat.etat AS chat_etat,
chat.reply_to AS reply_to,
chat_from.from_id AS from_id,
chat_from.type AS from_type,
chat_to.to_id AS to_id,
chat_to.type AS to_type,
chat_projet.projet_id AS projet_id,
chat_projet.task_id AS task_id,
uto.username AS to_username,
ufrom.username AS from_username
FROM chat
INNER JOIN chat_from ON chat.id = chat_from.chat_id
INNER JOIN chat_to ON chat.id = chat_to.chat_id
INNER JOIN chat_projet ON chat.id = chat_projet.chat_id
INNER JOIN users AS uto ON uto.id = chat_to.to_id
INNER JOIN users AS ufrom ON ufrom.id = chat_from.from_id
我不知道这是否是作为 SQL 学徒的最佳实践,但现在我可以区分发送用户和接收用户。