使用sql更新包含由|分隔的列表的列

Question

我有一个名为hr_Folders的表，其中包含以下信息：

FolderId    FolderName  EmployeeId  NumberOfEntries IsDeleted   
1            Folder 1      9                 1              0   
2            Folder 2      9                 1              0   
3            Folder 3      9                 1              0   

还有另一个表hr_EmployeeSavedRegistrants，其中folderids保存为|分隔列表，如下所示：

ID  EmployeeID  RegistrantID    FolderID    
44    9           4           1|2   
45    9           5           1|3       
46    9           6           2

我想创建一个存储过程，当我传递folderid和employeeid时，它会从hr_folder中删除该文件夹，然后相应地更新hr_EmployeeSavedRegistrants表的FolderId列(从folderid列列表中删除folderid，如果hr_EmployeeSavedRegistrants中有一个folderid，则删除该行，如hr_EmployeeSavedRegistrants中的第3行)

到目前为止，编写的代码是：

CREATE PROC GetResumeCountInfolder

@Folder int

AS

DECLARE @FolderIDs VARCHAR(100)
SELECT @FolderIDs = FolderID from hr_EmployeeSavedRegistrants
where deleted = 0
and EmployeeID= 9

请建议一下怎么做。

谢谢

Answer 1

对于您的表结构，这里有一个解决方案，但是，我同意Mahmoud的观点--您真的应该规范化您的表。创建一个名为hr_EmployeeFolders的新表，并将EmployeeId和FolderId作为您的两个字段。然后，您可以存储1-n个员工和文件夹。以这种方式维护要容易得多。

但是，考虑到您当前的场景，应该可以使用下面这样的方法：

create proc updatefolder 
  @FolderId int, @EmployeeId int
as
begin
  update hr_Folders 
  set isdeleted = 1
  where folderId = @folderId and employeeId = @employeeId

  update hr_EmployeeSavedRegistrants 
  set FolderId = replace(replace(folderId, @FolderId, ''),'||','|')
  where employeeId = @employeeId

  update hr_EmployeeSavedRegistrants 
  set FolderId =
    case 
      when left(FolderId,1) = '|' then right(folderId, len(folderId) - 1)
      when right(FolderId,1) = '|' then left(folderId, len(folderId) - 1)
      else FolderId
    end
  where employeeId = @employeeId
end

SQL Fiddle Demo

有一些其他的选项拆分列表并重新创建，但这是我能想到的最快的。第一个update语句更新folders表中已删除的列。第二个update语句从EmployeeSavedRegistrants表中删除文件夹id，以及任何可能的双管道实例。最后的update语句删除所有开头或结尾的管道。