sql - 由于逗号分隔值，无法弄清楚如何连接表

Question

我有一个具有以下结构的表格。

select loginid,alloted_area from tbllogin

返回此结果。

loginid       alloted_area
------------- ---------------------------
01900017      22,153,169,174,179,301
01900117      254,91,92,285,286,287
01900217      2,690,326,327,336
17900501      null
17900601      28,513,409,410
17901101      254,91,92,285
17901701      59,1302,1303
17902101      2,690,326,327
17902301      20,159,371,161
17902401      null

我有另一个表tblarea，当将区域分配给用户时，其 id 存储在上表中的逗号分隔值中。我想连接这两个表并留下像尚未分配区域的最后一个表一样的条目。现在我已经多次被告知，以逗号分隔值存储数据是一种不好的做法(我想这是因为我面临的问题)我知道，但这个结构是由我公司的另一个开发人员创建的，而不是我，所以请帮忙而不是投反对票。这是我尝试过的:

declare @csv varchar(max)='';
SELECT @CSV = COALESCE(@CSV + ', ', '') + case when alloted_area is null or alloted_area='' then '0' else alloted_area end from tbllogin;
select * from tblarea where id in (select 0 union select sID from splitstring(@CSV,','));

这确实获得了该区域，但它无法为我提供该区域已分配给的用户的登录信息。输入和输出示例。

登录

 loginid       alloted_area
 ------------- ---------------------------
 a1            1,3,5
 a2            2,4
 a3            1,4
 a4            null

tbla区域

 id            area_name
 ------------- ---------------------------
 1             v
 2             w
 3             x
 4             y
 5             z

加入后我需要这个结果

 login_id            area_name
 ------------- ---------------------------
 a1             v
 a1             x
 a1             z
 a2             w
 a2             y
 a3             v
 a3             y 

Answer 1

通过使用 Split 和 CROSS APPLY 我们可以实现所需的输出

DECLARE @tbllogin TABLE (LoginID CHAR(2) NOT NULL PRIMARY KEY, alloted_area VARCHAR(MAX));
INSERT @tblLogin (LoginID, alloted_area)
VALUES ('a1', '1,3,5'), ('a2', '2,4'),('a3', '1,4'), ('a4', NULL);

DECLARE @tblArea TABLE (ID INT NOT NULL PRIMARY KEY, Area_Name CHAR(1));
INSERT @tblArea (ID, Area_Name) 
VALUES (1, 'v'), (2, 'w'), (3, 'x'), (4, 'y'), (5, 'z');


SELECT Dt.LoginID,A.Area_Name FROm
(
SELECT LoginID,Split.a.value('.', 'VARCHAR(1000)') AS alloted_area
            FROM (
                SELECT LoginID,CAST('<S>' + REPLACE(alloted_area, ',', '</S><S>') + '</S>' AS XML) AS alloted_area
                FROM @tbllogin
                ) AS A
            CROSS APPLY alloted_area.nodes('/S') AS Split(a)

)DT
Inner join 
 @tblArea A
 on A.ID=DT.alloted_area

输出

LoginID     Area_Name
--------------------
a1          v
a1          x
a1          z
a2          w
a2          y
a3          v
a3          y