具有三个选项的 where 子句

Question

我正在使用SQL Server 2016 Dev，我有一个包含3个选项的WHERE子句。

我解决了 2 个，我需要第三个帮助。请参阅代码。

我不能使用 CTE 或任何其他代码，因为它是旧代码的一部分，除了 where 子句上的情况外，无法修改。

要求

When DepartmentID = 99 then all records
When DepartmentID = 97 then only PriorityID 10 records
All other Departments = all records except PriorityID 10

示例数据

Create table Department
(
DepartmentID int null,
PriorityID   int null
)
GO
Insert into Department (DepartmentID, PriorityID) 
values (1, 2), (2, 2), (3, 10), (4, 3), (5, 4), 
(97, 10), (99, 10), (4, 5), (5, 3), (2, 10),
(99, 2), (97, 1), (3, 2), (3, 3), (2, 5)
GO
Select * from Department

到目前为止我的查询

--Declare @intDepartmentID int = 99 OK
--Declare @intDepartmentID int = 97 OK
Declare @intDepartmentID int = 2 -- Need to exclude Record with PriorityID = 10
Declare @intPriorityID int = null
IF @intDepartmentID = 99
Begin
Set @intDepartmentID = null
Set @intPriorityID = null
End
If @intDepartmentID = 97
Begin 
Set @intDepartmentID = null
Set @intPriorityID = 10
End
Select DepartmentID,
PriorityID
From Department
Where (DepartmentID = @intDepartmentID or @intDepartmentID is null)
-- I think I need case statement here?
And (PriorityID = @intPriorityID or @intPriorityID is null)

Answer 1

请检查此查询。

SELECT DepartmentID,
PriorityID
FROM Department
WHERE ((PriorityID != (CASE  
WHEN (@intDepartmentID NOT IN (97, 99)) THEN 10
END) AND @intDepartmentID NOT IN (97,99))
OR (PriorityID = (CASE  
WHEN @intDepartmentID = 99 THEN PriorityID
WHEN @intDepartmentID = 97 THEN 10
END) AND @intDepartmentID IN (97,99))
)
--AND (DepartmentID = @intDepartmentID OR @intDepartmentID is null)  --If you want to filter data by department then enable this condition

Answer 2

首先，您不需要将@intPriorityID作为参数之一，因为 PriorityID 值由@intDepartmentID确定。

请尝试此操作，并让我知道这是否是您需要的：

SELECT DepartmentID,
PriorityID
FROM Department
WHERE (1 =
(CASE WHEN @intDepartmentID = 99 THEN 1 END)
OR
PriorityID = 
(CASE WHEN @intDepartmentID = 97 THEN 10 END)
OR
PriorityID <> 
(CASE WHEN @intDepartmentID NOT IN (99,97) THEN 10 END)
)

Answer 3

您确实应该在问题中包含参数每个值的预期结果。

根据您对其他答案的评论，我认为您确实希望按DepartmentID进行过滤，而不管@intDepartmentID参数的值如何，并使用一些额外的逻辑来确定是否也要按PriorityID进行过滤。

这是一种方法。

SELECT * 
FROM @Department AS D
WHERE
(D.DepartmentID = @intDepartmentID)
AND
(
(@intDepartmentID = 99)
OR
(@intDepartmentID = 97 AND D.PriorityID = 10)
OR
(@intDepartmentID NOT IN (97, 99) AND D.PriorityID <> 10)
)
OPTION(RECOMPILE);

上面如何写比较的简单方法假设DepartmentID、PriorityID和@intDepartmentID不能NULL。 如果它们可以NULL，如示例数据中的CREATE TABLE语句所示，请在示例数据中添加一些带有 NULL 的相关行，以说明数据中的所有情况。此外，请确保在问题中包含预期结果。

这种查询容易出现参数嗅探问题，这就是我添加OPTION(RECOMPILE)的原因。如果不这样做，此查询的性能可能取决于您用于首次运行它的参数的值。

Answer 4

如果我理解了这个问题：

1)使用@intDepartmentID = 99您希望所有记录都没有过滤器。
2) 使用@intDepartmentID = 97您希望所有记录都带有PriorityID = 10
3) 使用@intDepartmentID NOT IN (97, 99)您希望指定部门ID的所有记录，但带有PriorityID <> 10

对于您的测试数据，这意味着：

DepID   PriID   Visible if:
1       2       @intDepartmentID = 99,  @intDepartmentID = 1
2       2       @intDepartmentID = 99,  @intDepartmentID = 2
3       10      @intDepartmentID = 99,  @intDepartmentID = 3,  @intDepartmentID = 97
4       3       @intDepartmentID = 99,  @intDepartmentID = 4
5       4       @intDepartmentID = 99,  @intDepartmentID = 5
97      10      @intDepartmentID = 99,  @intDepartmentID = 97
99      10      @intDepartmentID = 99,  @intDepartmentID = 97
4       5       @intDepartmentID = 99,  @intDepartmentID = 4
5       3       @intDepartmentID = 99,  @intDepartmentID = 5
2       10      @intDepartmentID = 99,  @intDepartmentID = 2,  @intDepartmentID = 97
99      2       @intDepartmentID = 99  
97      1       @intDepartmentID = 99  
3       2       @intDepartmentID = 99,  @intDepartmentID = 3
3       3       @intDepartmentID = 99,  @intDepartmentID = 3
2       5       @intDepartmentID = 99,  @intDepartmentID = 2

这是查询：

Select * 
from Department
where 
case @intDepartmentID 
when 99 then 1 
when 97 then 
case when (PriorityID = 10) then 1 else 0 end
else
case when ((@intDepartmentID = DepartmentID) and (PriorityID <> 10)) then 1 
else 0 end
end = 1

Answer 5

where DepartmemtID = @DepartmentID and
<add one of the two case expressions below>
case @DepartmentID
when 99 then 1
when 97 then case when PriorityID = 10 then 1 end
else case when PriorityID <> 10 then 1 end
end = 1
-- or
case 
when @DepartmentID = 99 then 1
when @DepartmentID = 97 and PriorityID  = 10 then 1
when @DepartmentID = 97 then 0 -- all other priorities
when PriorityID <> 10 then 1
end = 1

第二个是不太嵌套的，正如所写的，它确实有点依赖于早期案例的失败。此外，如果您有空值，您也需要进行调整。

一般的想法是使用case仅针对您感兴趣的条件返回匹配值(在本例中为 1)。随着这些变得越来越复杂，能够依靠早期的案例来压缩逻辑是错误的，这可能是很好的，我认为它通常也更容易维护。请记住，顺序很重要。

Answer 6

最后，这按预期工作

Where 
( 
PriorityID = (CASE @intDepartmentID WHEN 99 then PriorityID  WHEN 97 then 10 END )
OR  (PriorityID != case when @intDepartmentID not in (99,97) then 10 end )
and (DepartmentID = @intDepartmentID )
)