sql - SQL Server-从2列获取唯一ID并将其存储在一个列表中

我有下表

user_one    user_two
20151844    2016000
20151844    2017000
2018000     20151844
20151844    20151025
20151036    20151844

由以下查询生成

 select * from [dbo].[Contact] C
 where C.user_one=20151844 or C.user_two=20151844

我想得到以下结果，不包括当前用户id 20151844

contact_Ids
2016000
2017000
2018000
20151025
20151036

实现这一目标的最佳优化方法是什么？知道我想加入id以从用户表中获取联系人姓名。
这是我的桌子：

Contact
user_one (int FK User.user_id), user_two (int FK User.user_id), status, action_user (int)

User
user_id (int PK), name , ...

最佳答案

使用UNION和INNER JOIN：

SELECT c.[contact_Ids],
       u.[name]
       FROM (SELECT [user_one] [contact_Ids]
                    FROM [Contact]
                    WHERE [user_one] <> 20151844
                          AND [user_two] = 20151844
             UNION
             SELECT [user_two] [contact_Ids]
                    FROM [Contact]
                    WHERE [user_two] <> 20151844
                          AND [user_one] = 20151844) c
             INNER JOIN [User] u
                        ON u.[user_id] = c.[contact_Ids]
       ORDER BY c.[contact_Ids];