来自自联接表和另一个MySQL的结果集

Question

DB: MySql

我有两张桌子-- MoneyExchange.和Student学生有StudentId，FirstName和LastName栏目。MoneyExchange有列MoneyExchange、、StudentIdTo、和#en0#。这张桌子上将有学生之间交换钱的细节。StudentIdFrom和StudentIDTo都引用了“学生表”中的StudentId列。

我想构建一个具有以下结果集的查询：

StudentIdFrom，FirstNameFrom，LastNameFrom，StudentIdTo，FirstNameTo，LastNameTo，Amount (数量的前15位)。

Answer 1

您需要对表进行别名，以便可以同时引用它们：

select
    money_ex.StudentIdFrom,
    st_from.FirstName,
    st_from.LastName,
    money_ex.StudentIdTo,
    st_to.FirstName,
    st_to.LastName,
    money_ex.Amount
from
    MoneyExchange as money_ex
    join Student as st_from on money_ex.StudentIdFrom = st_from.StudentId
    join Student as st_to on money_ex.StudentIdTo = st_to.StudentId
order by
    money_ex.Amount asc
limit 0 , 15;