带有AND和OR运算符的多个筛选器给出错误的结果

Question

我正在尝试基于以下过滤器获取员工列表：

DepartmentId 
DivisionId

用例1:如果用户只选择部门作为过滤器，则忽略DivisionId并仅按部门Id 获取员工

用例2:如果用户同时选择了部门和部门作为过滤器，则按部门ID和部门ID 获取员工

用例3:如果未选择部门，则获取所有员工(放弃部门和部门过滤器(

查询：

DECLARE @departmentId AS int, @divisionId AS int
set @departmentId = 1
set @divisionId = 100
select * from Employees where 
(DepartmentId is null  or 
(DepartmentId = @departmentId and 
(DivisionId is null or DivisionId = @divisionId)))

这里的问题是，我甚至得到DepartmentId和DivisionId都为空的记录，而我只想要一个DepartmentId = 1 and DivisionId = 100的记录

有人能帮我解决这个逻辑问题吗？

Answer 1

我猜你想要：

where (@DepartmentId is null or DepartmentId = @departmentId) and 
(@DivisionId is null or DivisionId = @divisionId)

Answer 2

试试这个：

where (@departmentID is null) or
(@divisionID is null and DepartmentID = @departmentID) or
(DepartmentID = @departmentID and DivisionID = @divisionID)