在sql-server中的表中查找分隔值

In a table A i have a column (varchar*30) city-id with the value e.g. 1,2,3 or 2,4. The description of the value is stored in another table B, e.g. 1 Amsterdam 2 The Hague 3 Maastricht 4 Rotterdam

在表A中，我有一个列（varchar * 30）city-id，其值为例如1,2,3或2,4。该值的描述存储在另一个表B中，例如， 1阿姆斯特丹2海牙3马斯特里赫特4鹿特丹

How must i join table A with table B to get the descriptions in one or maybe more rows?

我如何将表A与表B连接以获得一行或多行中的描述？

2 个解决方案

#1

Assuming this is what you meant:

Table A:
id
-------
1
2
3

Table B:
id |  Place
-----------
1  | Amsterdam 
2  | The Hague
3  | Maastricht 
4  | Rotterdam

Keep id column in both tables as auto increment, and PK.
Then just do a simple inner join.

select * from A inner join B on (A.id = B.id);

#2

Ideal way to deal with such scenarios is to have a normalized table as Collin. In case that can't be done here is the way to go about -

处理这种情况的理想方法是将标准化表格作为Collin。如果这里无法做到的话就是这样 -

You would need to use a table-valued function to split the comma-seperated value. If you are having SQL-Server 2016, there is a built-in SPLIT_STRING function, if not you would need to create one as shown in this link.

您需要使用表值函数来拆分逗号分隔值。如果你有SQL-Server 2016，有一个内置的SPLIT_STRING函数，如果没有，你需要创建一个，如此链接所示。

create table dbo.sCity(
    CityId varchar(30)
);

create table dbo.sCityDescription(
    CityId              int
    ,CityDescription    varchar(30)
);

insert into dbo.sCity values
('1,2,3')
,('2,4');

insert into dbo.sCityDescription values
(1,'Amsterdam')
,(2,'The Hague')
,(3,'Maastricht')
,(4,'Rotterdam');


select  ctds.CityDescription
        ,sst.Value as 'CityId'
from    dbo.sCity ct
        cross apply dbo.SplitString(CityId,',') sst
        join dbo.sCityDescription ctds
            on sst.Value = ctds.CityId;