左联接使用逗号分隔列使用sql

Question

我正在使用SQL server数据库开发一个asp.net应用程序。这个数据库有两个表Vacancies和dutystations。Vacations表有一个名为dutystationId的列，它将dutystations的ID存储在逗号分隔的列表中，如下所示：

2,12,15,18,19,23

现在我想在网格中显示这个空缺，我已经使用了这样的左联接：

查询

SELECT * FROM dbo.hr_Vacancies 
CROSS APPLY dbo.hr_Split(dbo.hr_Vacancies.DutyStationID, ',') AS s
LEFT OUTER JOIN dbo.hr_DutyStations 
ON s.Data = dbo.hr_DutyStations.DutyStationID

并且在xsd中我已经将vacancyid设置为主键。但我得到错误：

错误

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

如果我去掉这个约束，我得到6行。我只想显示一行。我该怎么做？

Answer 1

我停止阅读此处：

Vacations表有一个名为dutystationId的列，它将dutystation的ID存储在逗号分隔的列表中

这就是你的问题。如果RDBMS中有逗号分隔的值，特别是如果它们包含其他表的外键，则应该halt完全停止您正在做的任何事情，并开始重新设计数据库。RDBMS中的多对多关系是用连接表实现的，如果使用它们，所有问题都会突然自行解决。

您当前的设计不仅是编写SQL查询的地狱，就像这个问题完美地说明的那样，因为您无法解决一个琐碎的任务，而且它还会扼杀性能——这些对hr_Split的调用在计算上比仅仅执行正确的联接要贵得多。

不要陷入XY陷阱，先解决真正的问题。也就是说，你现在甚至违反了第一范式。