SQL查询从表中获取多次信息 - SQL query taking several times information from a table

SQL query taking several times information from a table

本文关键字：信息获取查询 SQL | 更新日期: 2023-12-07

我想这对实验过的Sql用户来说很容易：

给定以下表格：

表用户

   id  Name
   1 | Stephan
   2 | Marion
   3 | Jonathan
   4 | Morgan

表消息

  id| sender | reveiver | message
   1|    2   |     4    | "hello world!"

您将如何进行请求以获得以下内容（仅在一个sql请求中）：

 id| sender | reveiver | message        | sender.username | receiver.username
  1|    2   |     4    | "hello world!" |       Marion    |        Morgan

非常感谢！

尝试

select m.id, sender, receiver, message, sender.name, receiver.name
from messages m
left outer join users sender on sender.id = m.sender
left outer join users receiver on receiver.id = m.receiver

select m.id, sender, reveiver, message,
  sender.Name as sender_username, receiver.Name as receiver_username
from messages m
inner join users sender on sender.id = m.sender
inner join users receiver on receiver.id = m.reveiver

SELECT
    m.id
    , m.sender
    , m.receiver
    , m.message
    , s.name
    , r.name
FROM
    messages m
    , INNER JOIN names s ON (s.id = m.sender)
    , INNER JOIN names r ON (r.id = m.receiver)

    select
        m.id
    ,  m.sender
    ,  m.receiver
    ,  m.message
    ,  su.user
    ,  ru.user
    from message m
    join user su on (su.id = m.sender)
    join user ru on (ru.id = m.receiver)

我相信有一种更有效的方法，但这可能是最简单的。