我的查询过滤没有按照我想要的方式工作

Question

我想通过 3 种方式进行过滤：

1. 按名称
2. 、按列表
3. 并显示

我正在使用 ASP.NET 3.5 和 SQL Server 2008 的所有内容。使用 ADO.NET 和存储过程。

我将列表作为表值参数传递（但我正在使用表变量进行测试）并将名称作为 nvarchar 传递。我将“显示全部”设置为 ISNULL(@var,column)=column。显然，我查询此问题的方式并没有利用短路，或者我对 WHERE 子句如何工作的理解缺乏。发生的情况是，如果我使 @var = 'some string' 并向表变量插入 null，则它会正确过滤。如果我使 @var = null 并将“某个字符串”插入到表变量中，那么我会得到每条记录，我应该在其中获取“某个字符串”。

代码：

declare @resp1 nvarchar(32)
set @resp1 = null
declare @usersTable table
(responsible nvarchar(32))
--insert into @usersTable (responsible) values (null)
insert into @usersTable (responsible) values ('ssimpson')
insert into @usersTable (responsible) values ('kwilcox')
select uT.responsible, jsq.jobnumber, jsq.qid, aq.question, aq.section, aq.seq, answers.* 
from answers
inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid
inner join apqp_questions as aq on jsq.qid = aq.qid
left join @usersTable as uT on uT.responsible = answers.responsible
where answers.taskAction = 1 and (uT.responsible is not null or ISNULL(@resp1, Answers.responsible) = Answers.responsible)
order by aq.section, jsq.jobnumber, answers.priority, aq.seq

这是我想出的。虽然很丑……

declare @resp1 nvarchar(32)
set @resp1 = 'rrox'
declare @filterPick int
declare @usersTable table
(responsible nvarchar(32))
insert into @usersTable (responsible) values (null)
--insert into @usersTable (responsible) values ('ssimpson')
--insert into @usersTable (responsible) values ('kwilcox')
if @resp1 is null 
begin
   set @filterPick = 2
end
else
begin
   set @filterPick = 1
end
select uT.responsible, jsq.jobnumber, jsq.qid, aq.question, aq.section, aq.seq, answers.* 
from answers
inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid
inner join apqp_questions as aq on jsq.qid = aq.qid
left join @usersTable as uT on uT.responsible = answers.responsible
where answers.taskAction = 1 and 
(case
    when uT.responsible is not null then 2
    when ISNULL(@resp1, Answers.responsible) = Answers.responsible then 1          
 end = @filterPick )
order by aq.section, jsq.jobnumber, answers.priority, aq.seq

好吧。我想我已经明白了。我删除了@resp1，因为它没有必要，我只是使用表值参数@usersTable（但在这里我使用表变量进行测试）。我添加了一个标志 @filterPick，这样我就可以只显示 @usersTable 中的值或answers.taskAction = 1 的每条记录。

代码：

declare @filterPick bit 
declare @usersTable table
(responsible nvarchar(32))
insert into @usersTable (responsible) values (null)
--insert into @usersTable (responsible) values ('ssimpson')
--insert into @usersTable (responsible) values ('kwilcox')
if exists (select * from @usersTable where responsible is not null)
   begin
      set @filterPick = 1
   end
else
   begin
      set @filterPick = 0
   end
select *
from answers
inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid
inner join apqp_questions as aq on jsq.qid = aq.qid
left join @usersTable as uT on answers.responsible = uT.responsible
where answers.taskAction = 1 and (uT.responsible is not null or (isnull(uT.responsible, answers.responsible) = answers.responsible and @filterPick = 0))
order by aq.section, jsq.jobnumber, answers.priority, aq.seq

Answer 1

我对你的问题有点困惑，但我会尝试一下。

首先，我怀疑您返回的不正确记录的问题与您对空值的比较有关。为了演示我正在谈论的内容，请查询您想要的任何表并将其添加到末尾：

WHERE null = null

不会返回任何记录。在您的代码中，我将更改：

where answers.taskAction = 1 and (uT.responsible is not null or ISNULL(@resp1, Answers.responsible) = Answers.responsible)

到

where answers.taskAction = 1 and (uT.responsible is not null or @resp1 is null)

并查看是否返回所需的结果。

Answer 2

您是否考虑过将 WHERE 子句更改为：

WHERE Answers.taskAction = 1 
AND(ISNULL(@resp1, Answers.responsible) = Answers.responsible 
    OR Answers.responsible IN (SELECT responsible FROM uT))

而不是 JOINing 表值参数？