在以逗号分隔的列表中查找经理 [英] Find manager in a comma-separated list

问题描述

我有一个带有ID和负责人经理的Projects表。 负责人的经理列的值对于项目1为 John，Jim ，对于项目2为 Jim，Julie 。 >

但是如果我将Jim传递给存储过程，我应该得到2个项目（1,2）。由于列为 John，Jim ，因此不会返回任何行，但是SQL Server正在查找 ='Jim'：

 选择不同的ID，即从Manager = @ Manager 
  

解决方案

  WHERE'，'+ Manager +'，'LIKE'％，Jim，％' 
  

或者我想匹配您的实际代码：

  WHERE'，'+ Manager +'，'LIKE'％，'+ @Manager +'，％'
  

请注意，您的设计存在严重缺陷。完全没有理由将名称存储在该表中，不用担心任何数据点的逗号分隔列表。这些事实本身很重要，所以要这样对待它们！

 创建表dbo.Managers 
（
 ManagerID INT主键，
名称NVARCHAR（64）NOT NULL UNIQUE，... 
）; 
 
创建表dbo.Projects 
（
 ProjectID INT PRIMARY KEY，
 Name NVARCHAR（64）NOT NULL UNIQUE，... 
）; 
 
创建表dbo.ProjectManagers 
（
 ProjectID INT非空外键引用dbo.Projects（ProjectID），
 ManagerID INT非空外键引用dbo.Managers （ManagerID）
）; 
  

现在设置您提到的示例数据：

  INSERT dbo.Managers（ManagerID，Name）
 VALUES（1，N'John'），（2，N'Jim'），（3，N'朱丽叶'）; 
 
插入dbo.Projects（ProjectID，名称）
 VALUES（1，N'项目1'），（2，N'项目2'）; 
 
插入dbo.ProjectManagers（ProjectID，ManagerID）
 VALUES（1,1），（1,2 ,,（2,2），（2,3）; 
  

现在可以找到Jim管理的所有项目：

  DECLARE @Manager NVARCHAR（32）= N'Jim'; 
 
选择p.ProjectID，p.Name 
从dbo.Projects AS p 
 INNER JOIN dbo.ProjectManagers AS pm 
 ON p.ProjectID = pm.ProjectID 
内部连接dbo.Managers AS m 
 ON pm.ManagerID = m.ManagerID 
 WHERE m.name = @Manager; 
  

或者您甚至可以手动短路一下：

  DECLARE @Manager NVARCHAR（32）= N'Jim'; 
 
 
声明@ManagerID INT; 
从dbo中选择@ManagerID = ManagerID 
.Managers 
 WHERE Name = @Manager; 
 
选择p.ProjectID，p.Name 
从dbo.Projects AS p 
 INNER JOIN dbo.ProjectManagers AS pm 
 ON p.ProjectID = pm.ProjectID 
 WHERE pm.ManagerID = @ManagerID; 
  

甚至更多：

  DECLARE @Manager NVARCHAR（32）= N'Jim'; 
 
 
声明@ManagerID INT; 
从dbo中选择@ManagerID = ManagerID 
.Managers 
 WHERE Name = @Manager; 
 
从dbo中选择ProjectID，命名
.Projects AS p 
存在
（
从dbo.ProjectManagers pm中选择1 
 pm 
 pm.ProjectID = p.ProjectID 
 AND pm.ManagerID = @ManagerID 
）； 
  

顺便说一句，我真的，真的，非常希望 DISTINCT 在原始查询中是不必要的。您确实有多个具有相同名称​​和 ID的项目吗？

I have a Projects table with ID and Responsible manager. The Responsible manager columns has values as John,Jim for Project 1 and Jim,Julie for Project 2.

But if I pass Jim to my stored procedure I should get 2 projects (1,2). This returns no rows because the column is John,Jim but SQL Server is looking for ='Jim':

 select distinct ID,Manager from Projects where Manager=@Manager

解决方案

WHERE ',' + Manager + ',' LIKE '%,Jim,%'

Or I suppose to match your actual code:

WHERE ',' + Manager + ',' LIKE '%,' + @Manager + ',%'

Note that your design is extremely flawed. There is no reason you should be storing names in this table at all, never mind a comma-separated list of any data points. These facts are important on their own, so treat them that way!

CREATE TABLE dbo.Managers
(
  ManagerID INT PRIMARY KEY,
  Name NVARCHAR(64) NOT NULL UNIQUE, ...
);

CREATE TABLE dbo.Projects
(
  ProjectID INT PRIMARY KEY,
  Name NVARCHAR(64) NOT NULL UNIQUE, ...
);

CREATE TABLE dbo.ProjectManagers
(
  ProjectID INT NOT NULL FOREIGN KEY REFERENCES dbo.Projects(ProjectID),
  ManagerID INT NOT NULL FOREIGN KEY REFERENCES dbo.Managers(ManagerID)
);

Now to set up the sample data you mentioned:

INSERT dbo.Managers(ManagerID, Name)
  VALUES(1,N'John'),(2,N'Jim'),(3,N'Julie');

INSERT dbo.Projects(ProjectID, Name)
  VALUES(1,N'Project 1'),(2,N'Project 2');

INSERT dbo.ProjectManagers(ProjectID,ManagerID)
  VALUES(1,1),(1,2),(2,2),(2,3);

Now to find all the projects Jim is managing:

DECLARE @Manager NVARCHAR(32) = N'Jim';

SELECT p.ProjectID, p.Name
  FROM dbo.Projects AS p
  INNER JOIN dbo.ProjectManagers AS pm
  ON p.ProjectID = pm.ProjectID
  INNER JOIN dbo.Managers AS m
  ON pm.ManagerID = m.ManagerID
  WHERE m.name = @Manager;

Or you can even manually short circuit a bit:

DECLARE @Manager NVARCHAR(32) = N'Jim';


DECLARE @ManagerID INT;
SELECT @ManagerID = ManagerID
  FROM dbo.Managers
  WHERE Name = @Manager;

SELECT p.ProjectID, p.Name
  FROM dbo.Projects AS p
  INNER JOIN dbo.ProjectManagers AS pm
  ON p.ProjectID = pm.ProjectID
  WHERE pm.ManagerID = @ManagerID;

Or even more:

DECLARE @Manager NVARCHAR(32) = N'Jim';


DECLARE @ManagerID INT;
SELECT @ManagerID = ManagerID
  FROM dbo.Managers
  WHERE Name = @Manager;

SELECT ProjectID, Name
  FROM dbo.Projects AS p
  WHERE EXISTS 
  (
    SELECT 1 
      FROM dbo.ProjectManagers AS pm
      WHERE pm.ProjectID = p.ProjectID
      AND pm.ManagerID = @ManagerID
  );

As an aside, I really, really, really hope the DISTINCT in your original query is unnecessary. Do you really have more than one project with the same name and ID?

这篇关于在以逗号分隔的列表中查找经理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！