如何将两列连接到同一张表 [英] How to join two columns to the same table

问题描述

我有一个称为message的SQL表，它有三列

I have an SQL table called messages, it has three columns

1. UserFrom uniqueidentifier
2. UserTo uniqueidentifier
3. Messagen varchar(50)

此表用于存储从一个用户发送到另一个用户的消息，它存储来自aspnet_Users而不是username的UserId，现在我需要创建一个显示UserFrom和UserTo作为名称，方法是使用表消息中的UserId从aspnet_Users表中获取Usename.

This table is used to store messages sent from one user to another, it stores the UserId from the aspnet_Users instead of the username, now I need to create a view that shows the UserFrom and UserTo as names by getting the Usename from the aspnet_Users table using the UserId in the table messages.

预先感谢

推荐答案

您需要使用不同的别名连接两次aspnet_Users表:

You need to join aspnet_Users table twice with different alias names:

SELECT U1.Username as UserFrom,U2.Username as UserTo, M.Message
FROM Messages M JOIN
     aspnet_Users U1 ON U1.UserId=M.UserFrom JOIN
     aspnet_Users U2 ON U2.UserId=M.UserTo

说明:

在这里，aspnet_Users表以不同的别名U1，U2联接了两次.并且每个用户名都从相应的表中获取.

Here aspnet_Users table it joined twice with different alias names U1,U2. And each username is fetched from the respective table.

这篇关于如何将两列连接到同一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！