问题描述
我有一个带有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?
这篇关于在以逗号分隔的列表中查找经理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！