一列保存多个ID(将多个用逗号隔开的ID转换成用逗号隔开的名称)

在数据库设计中，有时会遇到一种情况，即在主表中的一列用来存储多个关联ID，这些ID之间用逗号隔开，这种做法虽然不符合数据库的第一范式，但在某些场景下却被广泛采用。例如，员工可能属于多个部门，每个员工的记录中就可能会有一个`deptIds`字段，里面存储了员工所属部门的ID序列。本文将以员工和部门为例，探讨如何将这些ID转换成对应的部门名称，以更直观地展示数据。 我们来看一个简单的例子，包含两个表：`Department`和`Employee`。`Department`表存储部门信息，包括`id`和`name`，而`Employee`表则包含员工信息，包括`id`、`name`以及`deptIds`字段，`deptIds`用于存储用逗号隔开的部门ID。初始化这两个表的数据后，我们的目标是获取每个员工对应的部门名称，而不是部门ID。 解决这个问题的一种方法是通过交叉连接和聚合函数。第一步，我们可以使用`OUTER APPLY`和自定义函数`fun_SplitIds`（该函数的作用是将逗号分隔的ID字符串拆分为单独的ID）将员工表和部门表关联。这将生成一个临时结果集，包含每个员工ID及其对应的部门名称。如果员工属于多个部门，那么对于每个部门ID都会有一条记录。 第二步，由于SQL Server没有内置的字符串聚合函数，我们需要自己实现这个功能。这里可以借鉴处理树形结构数据的方法，通过递归公共表表达式（CTE）来完成聚合。创建名为`EmployeT`的CTE，先将`deptIds`拆分成多行，并与部门表关联。接着，创建名为`mike`的CTE，为每个员工分配行号，以便后续聚合。通过`mike2`的CTE，将所有同ID的行聚合在一起，形成一个由逗号分隔的部门名称字符串。 整个过程的SQL语句大致如下： 1. 使用`OUTER APPLY`和`LEFT JOIN`获取初步结果： ```sql SELECT E.*, ISNULL(D.name, '') AS deptName FROM Employee AS E OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID LEFT JOIN Department AS D ON DID.ID = D.id; ``` 2. 创建CTE进行聚合： ```sql WITH EmployeT AS ( -- ... (步骤1的查询) ), mike AS ( -- ... (为每个员工ID分配行号) ), mike2 AS ( SELECT id, name, deptIds, CAST(deptName AS NVARCHAR(100)) AS deptName, level_num FROM mike WHERE level_num = 1 UNION ALL SELECT m.id, m.name, m.deptIds, CAST(m2.deptName + ',' + m.deptName AS NVARCHAR(100)) AS deptName, m.level_num FROM mike m JOIN mike2 m2 ON m.id = m2.id AND m.level_num = m2.level_num + 1 ) SELECT * FROM mike2; ``` 通过以上步骤，我们就能得到每个员工对应的逗号分隔的部门名称，从而满足了从列中多个逗号分隔的ID转换为部门名称的需求。然而，这种方法虽然实用，但并不理想，因为它不支持数据库的优化，且随着数据量的增长，性能可能会下降。在设计数据库时，通常建议遵循第一范式，将多对多关系通过中间表来表示，以提高数据管理和查询效率。