时间戳

首页 > 解决方案 > SQL 选择内连接 2 列

sql - SQL 选择内连接 2 列

问题描述

我有一些桌子，我们称它们为桌子UserManagement

ID  Username Position
1   Bryan    Client
2   Frey     Client
3   Will     Vendor
4   Anne     Vendor
5   Belle    Vendor

这是桌子Mailbox

ID  FromUser ToUser           Message
1   Bryan    Will,Anne,Belle  Hai
2   Anne     Bryan,Frey       Hello

我一直在尝试这个：

select * from Mailbox t
inner join (select Username from UserManagement where Position = 'Client' group by Username)um
on t.ToUser = um.Username

但是，当列ToUser仅包含用户名Bryan或Frey不包含用户名时，它是有效的，当列包含用户名Bryan,Frey 时如何解决这个问题？ToUserBryan,Frey

标签： sqlsql-serverinner-join

解决方案

这是一个从“mb_split_cte”CTE 中的邮箱表中拆分 ToUser 列的查询。然后它加入到用户名上的 UserManagement 表。然后它从 Mailbox 表中返回 ToUsers 的管理职位。

数据

DECLARE @Mailbox TABLE(ID int identity(1,1),
                       FromUser varchar(150),
                       ToUser varchar(150),
                       [Message] varchar(250));

INSERT INTO @Mailbox(FromUser, ToUser, [Message])
VALUES('Bryan', 'Will,Anne,Belle', 'Hai'),
('Anne', 'Bryan,Frey', 'Hello');

DECLARE @UserManagement TABLE(ID int identity(1,1),
                       Username varchar(150),
                       Position varchar(150));

INSERT INTO @UserManagement(Username, Position) VALUES
('Bryan', 'Client'),
('Frey', 'Client'),
('Will', 'Vendor'),
('Anne', 'Vendor'),
('Belle', 'Vendor');

询问

;with mb_split_cte(ID, FromUser, ToUser, [Message]) as (
    select ID, FromUser, sp.[value], [Message]
    from @Mailbox m
         cross apply string_split(m.ToUser, ',') sp)
select msc.*, um.Position as ToUserPosition
from mb_split_cte msc
     join @UserManagement um on msc.ToUser=um.Username;

输出

ID  FromUser    ToUser  Message ToUserPosition
1   Bryan       Will    Hai     Vendor
1   Bryan       Anne    Hai     Vendor
1   Bryan       Belle   Hai     Vendor
2   Anne        Bryan   Hello   Client
2   Anne        Frey    Hello   Client

推荐阅读