在 SQL 语句中将 NULL 作为参数传递时，如何调整 WHERE 子句以选择所有没有 Premium = 0 的记录 [英] How to tweak WHERE clause to select all records without Premium = 0 when passing NULL as a parameter in SQL statement

问题描述

结果应该返回:

• 如果 @reasonID = 1 我只需要选择具有 reasonID = 211
的策略
• 如果 @reasonID = 2 我只需要选择具有 reasonID <> 的策略211 包括reasonID IS NULL
• 如果 @reasonID = NULL 我需要选择所有策略，包括 NULL 和 Premium <>0

• If @reasonID = 1 I need to select only policies that have reasonID = 211
• If @reasonID = 2 I need to select only policies that have reasonID <> 211 including reasonID IS NULL
• If @reasonID = NULL I need to select all policies including NULL and Premium <> 0

以下示例适用于 @reasonID = 1 和 @reasonID = 2:

The below example works for @reasonID = 1 and @reasonID = 2:

@reasonID = 1

@reasonID = 2

但是如何在 @reasonID = NULL 时调整 WHERE 子句以选择所有行?因为它返回具有 Premium = 0 的策略，而我不需要.

But how can I tweak the WHERE clause to select all rows when @reasonID = NULL ? Because it returns policies that have Premium = 0, which I do not need.

@reasonID = NULL

declare @TempTable1 table (ControlNo int, PolicyNumber varchar(50), Premium money)

insert into @TempTable1 
values (1, 'Pol1', 100), (2, 'Pol2', 0), (3, 'Pol3', 50), (4, 'Pol4', 0),
       (5, 'Pol5', 70), (6, 'Pol6', 0), (7, 'Pol7', 30)

declare @TempTable2 table (ControlNo int, PolicyNumber varchar(50), reasonID int)

insert into @TempTable2 
values (1, 'Pol1', 5), (2, 'Pol2', NULL), (3, 'Pol3', 211),
       (4, 'Pol4', 8), (5, 'Pol5', 211), (6, 'Pol6', NULL),
       (7, 'Pol7', 3)

--select * from @TempTable1
--select * from @TempTable2

--Here I input @reasonID  parameter
declare @reasonID int = NULL

select
    T2.ControlNo, T2.PolicyNumber, T1.Premium, T2.reasonID 
from    
    @TempTable1 T1
inner join 
    @TempTable2 T2 on t1.ControlNo = T2.ControlNo
where   
    T1.Premium <> 0
    and (case when reasonID = 211 then 1 else 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
     or (@reasonID IS NULL) --does not work

但应该是这样的:

有没有什么方法可以在不使用HAVING 子句或GROUP BY 的情况下修改WHERE 子句以获得理想的结果?

Is any way to modify WHERE clause to achieve desirable result without using HAVING clause or GROUP BY?

推荐答案

我想你可能错过了添加一个括号.另外，我在 where 条件中修改了您的 case 语句，因为您的 else 条件也会考虑空值.由于您拥有或条件为空值，因此现在无关紧要.我假设您的 case 语句中可能不想要 null 条件，因此我对其进行了更改.我在您的输入中没有看到 Policy 8，因此它不会生成 8 的输出.其余相同.

I think you may have missed to add one parenthesis. Also, I modified your case statement in where condition because your else condition will take consideration of null values as well. Since you have or condtion for nulls it does not matter right now. I assumed you may not want null condition in your case statement so I changed it.I did not see Policy 8 in your input, so it does not generate the output of 8. rest is same.

declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
                               (2,'Pol2', 0),
                               (3,'Pol3', 50),
                               (4,'Pol4', 0),
                               (5,'Pol5', 70),
                               (6,'Pol6', 0),
                               (7, 'Pol7',30)

declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
                              (2,'Pol2', NULL),
                              (3,'Pol3', 211),
                              (4,'Pol4', 8),
                              (5,'Pol5', 211),
                              (6,'Pol6', NULL),
                              (7,'Pol7',3)
--select * from @TempTable1
--select * from @TempTable2

--Here I input @reasonID  parameter
declare @reasonID int = NULL

select  T2.ControlNo,T2.PolicyNumber, T1.Premium, T2.reasonID 
from    @TempTable1 T1
        inner join @TempTable2 T2 on t1.ControlNo = T2.ControlNo
where   T1.Premium <> 0
        and ((case when reasonID = 211 then 1 
         when isnull(reasonID,'') not in (211,'') then 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
            OR (@reasonID IS NULL)) --does not work (added parentheses)

输出:现在它不会带来溢价 <> 0

Output: Now it does not bring premium <> 0

ControlNo   PolicyNumber    Premium reasonID
1            Pol1             100.00    5
3            Pol3              50.00    211
5             Pol5             70.00    211
7             Pol7             30.00    3

这篇关于在 SQL 语句中将 NULL 作为参数传递时，如何调整 WHERE 子句以选择所有没有 Premium = 0 的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！