如何创建 SQL 运算符组合，如 ALL IN、NOT ALL IN

Question

我的 Filter 类中有以下 4 个属性。我将把以下 4 个属性解析为 StoredProcedure 并获取过滤结果。

/// <summary>
/// Gets and sets comma seperated condition ids.
/// Patients must have all these conditions in order to satisfy the filter.
/// </summary>
public string MustHaveAllConditions { get; set; }

/// <summary>
/// Gets and sets comma separated condition ids.
/// Patients must not have all of these conditions in order to satisfy the filter.
/// </summary>
public string MustNotHaveAllConditions { get; set; }

/// <summary>
/// Gets and sets comma separated condition ids.
/// Patients must have at least one of these conditions in order to satisfy the filter.
/// </summary>
public string MustHaveAtLeastOneCondition { get; set; }

/// <summary>
/// Gets and sets comma separated condition ids.
/// Patients must not have at least one of these conditions in order to satisfy the filter.
/// </summary>
public string MustNotHaveAtLeastOneCondition { get; set; }

我的存储过程将有四个参数，如下所示。 例如：

@*MustHaveAll*条件 = "1,2"

@*MustNotHaveAll*条件 = "3,4"

@*MustHaveAtLeastOne*条件= "5,6,7,8"

@*MustNotHaveAtLeastOne*Condition = "9, 10"

我正在使用 UDF返回一个包含 Ids 列的表。

我的问题：

基本上我可以使用 SQL“IN”运算符来查找至少具有一种条件（即： @MustHaveAtLeastOneCondition ）和“NOT IN”运算符组合来过滤 @MustNotHaveAnyConditions 的患者。

是否有任何 SQL 运算符（或简单的方法）来过滤 MustHaveAllConditions、MustNotHaveAllConditions 参数？

Answer 1

-- Patients
declare @Patient table (PatientID int)

-- Conditions per patient
declare @PatientCondition table (PatientID int, ConditionID int)

-- Conditions table generated from param string
declare @Condition table (ConditionID int)

-- Test data
insert into @Patient
select 1 union all
select 2 union all
select 3

insert into @PatientCondition
select 1, 1 union all
select 1, 2 union all
select 1, 3 union all
select 2, 1 union all
select 3, 3

insert into @Condition
select 1 union all
select 2


-- MustHaveAll
select *
from @Patient as P
where P.PatientID in 
  (
    select PC.PatientID
    from @PatientCondition as PC
      inner join @Condition as C
        on PC.ConditionID = C.ConditionID
    group by PC.PatientID
    having count(PC.ConditionID) = (select count(ConditionID) from @Condition)
  )

--MustNotHaveAll
select *
from @Patient as P
where P.PatientID not in 
  (
    select PC.PatientID
    from @PatientCondition as PC
      inner join @Condition as C
        on PC.ConditionID = C.ConditionID
    group by PC.PatientID
    having count(PC.ConditionID) = (select count(ConditionID) from @Condition)
  )

-- MustHaveAtLeastOne
select *
from @Patient as P
where P.PatientID in
  (
    select PC.PatientID
    from @PatientCondition as PC
      left outer join @Condition as C
        on PC.ConditionID = C.ConditionID
    where C.ConditionID is not null
  )

--MustNotHaveAtLeastOne
select *
from @Patient as P
where P.PatientID not in
  (
    select PC.PatientID
    from @PatientCondition as PC
      left outer join @Condition as C
        on PC.ConditionID = C.ConditionID
    where C.ConditionID is not null
  )

Answer 2

确实没有一种简单的方法可以完成您所描述的操作。我要做的可能是将您的字符串拆分为一个表变量，然后使用右连接、左连接和内连接的组合来从另一个表变量（即我的输出）中删除结果。