子查询返回了超过 1 个带有 WHERE CASE 的值

Question

我有 3 个代表对象的相关表：客户、用户和潜在客户。用户具有"用户"或"管理员"类型，可以创建潜在客户并为客户服务。在架构术语中，Users.Client引用Clients.Id多对一关系，Leads.CreatedBy引用Users.Username多对一关系。示例架构：

CREATE TABLE Clients (
Id INT IDENTITY PRIMARY KEY,
Name VARCHAR(32) NOT NULL
);
CREATE TABLE Users (
Id INT,
Username VARCHAR(32) NOT NULL,
Type VARCHAR(8) NOT NULL CHECK (Type IN ('Admin', 'User')),
Client INT NOT NULL,
PRIMARY KEY (Username),
UNIQUE (id),
FOREIGN KEY (Client) REFERENCES Clients (Id)
);
CREATE TABLE Leads (
Id INT IDENTITY PRIMARY KEY,
Name VARCHAR(64),
Company VARCHAR(64),
Profession VARCHAR(64),
CreatedBy VARCHAR(32) NOT NULL,
FOREIGN KEY (CreatedBy) REFERENCES Users (Username)
);

我正在编写一个查询来向用户显示他们的潜在客户。类型为"用户"的用户应该只能查看他们创建的潜在顾客。"管理员"类型的用户应该能够看到其客户的所有潜在客户(但不能看到其他客户的所有潜在客户)。哪个查询将根据这些限制从Leads表中获取行？我已经检查了其他问答，但我不知道如何将它们应用于上述情况。

我尝试了以下方法：

SELECT * 
FROM Leads 
WHERE createdby IN (
CASE 
WHEN (SELECT type 
FROM users 
WHERE username='Sathar'
)='Admin' 
THEN (
SELECT username 
FROM users 
WHERE client=(
SELECT client 
FROM users 
WHERE username='Sathar'
)   )
ELSE 'Sathar'
END
)

但是，它会生成错误：

子查询返回了 1 个以上的值。当子查询跟在 =、！=、<、<= 、>、>= 后面或子查询用作表达式时，不允许这样做。

为了完善此示例，提供了一些示例数据：

SET IDENTITY_INSERT Clients ON;
INSERT INTO Clients (Id, Name)
VALUES
(1, 'IDM'),
(2, 'FooCo')
;
SET IDENTITY_INSERT Clients OFF;
INSERT INTO Users (Id, Username, Type, Client)
VALUES
(1, 'Sathar', 'Admin', 1),
(2, 'bafh', 'Admin', 1),
(3, 'fred', 'User', 1),
(4, 'bloggs', 'User', 1),
(5, 'jadmin', 'Admin', 2),
(6, 'juser', 'User', 2)
;

INSERT INTO Leads (Name, Company, Profession, CreatedBy)
VALUES
('A. Person', 'team lead', 'A Co', 'Sathar'),
('A. Parrot', 'team mascot', 'B Co', 'Sathar'),
('Alice Adams', 'analyst', 'C Co', 'juser'),
('"Bob" Dobbs', 'Drilling Equipment Salesman', 'D Co', 'juser'),
('Carol Kent', 'consultant', 'E Co', 'juser'),
('John Q. Employee', 'employee', 'F Co', 'fred'),
('Jane Q. Employee', 'employee', 'G Co', 'fred'),
('Bob Howard', 'Detached Special Secretary', 'Capital Laundry Services', 'jadmin')
;

以上所有内容均可作为实时示例使用。

如果没有CASE表达式，查询不会生成错误，但不遵循所有限制(为类型User的用户返回客户端的所有潜在顾客)：

SELECT * 
FROM Leads 
WHERE createdby IN (
SELECT username 
FROM users 
WHERE client=(
SELECT client 
FROM users 
WHERE username='fred'
)
) 

这可以在另一个实时示例中显示的结果中看到。

Answer 1

这应该作为if else而不是作为case来完成，您可以像这样做：

if (SELECT type FROM users WHERE username='[the username]') = 'Admin'
begin
--The records you allow for admin to see for example
(SELECT * 
FROM Leads where createdby in 
(
select username 
from users where client=(select client from users where username='Sathar')
))
end
else
begin
--The records you allow for non admin to see for example
(SELECT * 
FROM Leads 
WHERE createdby = 'Sathar')
end

或者真的"管理员"应该用join来完成，但同样的想法。

之所以应该按照if else这样做，是因为case是一个表达式而不是一个语句(就像在其他语言中一样switch case)，而if else是一个条件语句，当你为每种类型的用户提供单独的select时，它会更好设计，它将帮助您不产生错误。

Answer 2

子查询是一种较旧的技术。虽然可以使用子查询编写生成所需结果的查询，但使用JOINs 通常更适合关系模型，因为JOINs 是关联表之间数据的基础操作。

JOIN的基础知识来自架构中定义的关系：外键。查询通常对每个外键都有一个JOIN。

起点

对于查询，有两个相关的数据片段：创建潜在顾客的用户和查看潜在顾客的用户。这表明应该两次咨询Users表：一次是主要创建者，一次是查看者(至少应该与创建者属于同一客户)。这给出了一个通用查询，然后可以对其进行修改以满足各种情况的条件(例如问题中的限制)：

SELECT Leads.*
FROM Leads
JOIN Users AS creator
ON Leads.CreatedBy = creator.Username
JOIN Users AS viewer
ON viewer.Client = creator.Client
WHERE viewer.Username = @user

连接条件中的限制

由于限制(和查询)是关于将查看者与他们可以查看的潜在顾客相关联，因此一种自然的方法是在viewer的联接条件中表达限制。在这种情况下，限制是：

1. 创作者和观看者相同，或者
2. 查看器的类型为"管理员"，与创建者属于同一客户端。

在 SQL 中：

creator.Id = viewer.Id
OR (    viewer.type = 'Admin'
AND viewer.Client = creator.Client)

由于我们正在重新定义行的关联方式，因此这些条件将替换一般连接条件(请注意，一般条件是这些条件的一部分)，从而提供：

SELECT Leads.*
FROM Leads
JOIN Users AS creator
ON Leads.CreatedBy = creator.Username
JOIN Users AS viewer
ON    viewer.Id = creator.Id
OR (    viewer.type = 'Admin'
AND viewer.Client = creator.Client)
WHERE viewer.Username = @user
;

您可以查看管理员和非特权用户的此查询结果。

筛选器中的限制

条件可以放在ON子句或WHERE子句中(在引入语法之前JOIN连接条件和过滤条件都WHERE)。如果从概念上讲，条件适用于与哪些行相关联，则它们通常应进入连接条件。如果条件在概念上限制(可能是中间)表的结果，则它们应放在WHERE子句中。按照我的思维方式，这种情况的条件是关于行关联的，因此应该放在ON子句中。但是，查看另一个选项很有用，特别是因为检查所有选项的查询执行计划可能会显示一个选项的性能更好(本答案中链接到的各种实时示例通过说明方式显示了示例数据的执行计划，但在为生产系统做出决策之前，应检查生产数据的执行计划)。

将限制条件添加到常规查询是一件简单的事情，从限制列表开始。由于"同一客户端"限制已存在于连接条件中，因此只需删除该部分限制，即可获得：

1. 创作者和观看者相同，或者
2. 查看器的类型为"管理员">

在 SQL 中：

creator.Id = viewer.Id
OR viewer.type = 'Admin'

接下来，将条件添加到WHERE子句。由于观众选择标准仍应适用，因此条件与AND相结合：

SELECT Leads.*
FROM Leads
JOIN Users AS creator
ON Leads.CreatedBy = creator.Username
JOIN Users AS viewer
ON viewer.Client = creator.Client
WHERE viewer.Username = @user
AND (   creator.Id = viewer.Id
OR viewer.type = 'Admin')

可以在实时示例中查看两种用户类型的结果(带有执行计划)。

Answer 3

使用这个：

select * from Leads where createdby in (case when (select TOP 1 type from 
users where username='Sathar')='Admin' then (select username from users 
where client=(select TOP 1 client from users where username='Sathar' )) else 
'Sathar' end);

从用户名='Sathar'的用户中选择类型，只会给出一种类型，所以最好使用TOP 1，从用户名='Sathar'的用户中选择客户端也是如此