SQL中的动态操作员

Question

假设我有下表Tbl_rules：

RuleID  NameOperator     NameValues   TypeOperator   TypeValue    
1       NotIn               John         In             2
1       NotIn               Alex         In            NULL
1       NotIn               Mike         In            NULL  
2        In                 Mike         NotIn          2

和我的源表看起来像tbl_source：

 ID Name   Type Cost  
 1  Mike    2   100   
 2  Cole    2   200  
 3  Ken     1   300  
 4  Tara    1   400  
 5  Mike    1   500
 6  Sonya   1   600 
 7  Ann     2   700
 8  Mike    1   800

我希望能够加入这两个表格并获得以下结果tbl_result：

RuleID  Name  Type  Cost
  1     Cole   2    200
  1     Ann    2    700  
  2     Mike   1    500  
  2     Mike   1    800   

如果我手动编写此查询，我的查询看起来像这样：

select 1, Name, Type, Cost 
from tbl_Source 
Where Name not in ('John', 'Alex', 'Mike') and Type in (2) 
union all
select 2, Name, Type, Cost
from tbl_Source
where Name in ('Mike') and Type not in (2)

在我当前的设置中TBL_RULE有500个记录，TBL_Source有500K记录。

对此的任何建议都非常感谢。限制：没有CLR功能，没有2017年功能(例如String_agg(

更新：可以在此处找到上述示例的DDL语句：http：//sqlfiddle.com/#！18/9a29f/2/2/2/0

Answer 1

这是一种方法。我已经使用cross join检查规则。动态SQL可能有更好的方法，其中join中实现了规则

declare @tbl_Rules table(
    RuleID int
    , NameOperator varchar(20)
    , NameValues varchar(20)
    , TypeOperator varchar(20)
    , TypeValue int
)
insert into @tbl_Rules
values 
(1, 'NotIn', 'John', 'In', 2)
, (1, 'NotIn', 'Alex', 'In', NULL)
, (1, 'NotIn', 'Mike', 'In', NULL)
, (2, 'In', 'Mike', 'NotIn', 2)
declare @tbl_Source table (
    ID int
    , Name varchar(20)
    , Type int
    , Cost int
)
insert into @tbl_Source
values
(1, 'Mike', 2, 100)
, (2, 'Cole', 2, 200)
, (3, 'Ken', 1, 300) 
, (4, 'Tara', 1, 400)
, (5, 'Mike', 1, 500)
, (6, 'Sonya', 1, 600) 
, (7, 'Ann', 2, 700)
, (8, 'Mike', 1, 800)
;with cte as (
    select
        distinct Ruleid, a.NameOperator, a.TypeOperator
         , NameValues = (
            select
                '!' + b.NameValues
            from
                @tbl_Rules b
            where
                a.RuleID = b.RuleID
                and b.NameValues is not null
            for xml path('')
        ) + '!'
        , TypeValue = (
            select
                concat('!', b.TypeValue)
            from
                @tbl_Rules b
            where
                a.RuleID = b.RuleID
                and b.TypeValue is not null
            for xml path('')
        ) + '!'
    from 
        @tbl_Rules a
)
select
    b.RuleID, a.Name, a.Type, a.Cost
from
    @tbl_Source a
    cross join cte b
where
    1 = case
        when b.NameOperator = 'In' and charindex('!' + a.Name + '!', b.NameValues) > 0 and b.TypeOperator = 'In' and charindex(concat('!', a.Type, '!'), b.TypeValue) > 0 then 1
        when b.NameOperator = 'In' and charindex('!' + a.Name + '!', b.NameValues) > 0 and b.TypeOperator = 'Notin' and charindex(concat('!', a.Type, '!'), b.TypeValue) = 0 then 1
        when b.NameOperator = 'NotIn' and charindex('!' + a.Name + '!', b.NameValues) = 0 and b.TypeOperator = 'In' and charindex(concat('!', a.Type, '!'), b.TypeValue) > 0 then 1
        when b.NameOperator = 'NotIn' and charindex('!' + a.Name + '!', b.NameValues) = 0 and b.TypeOperator = 'NotIn' and charindex(concat('!', a.Type, '!'), b.TypeValue) = 0 then 1
        else 0
    end

输出：

RuleID   Name   Type   Cost
---------------------------
1        Cole   2      200
1        Ann    2      700
2        Mike   1      500
2        Mike   1      800

Answer 2

您可以尝试。

SELECT MAX(R_N.RuleID) RuleID,  S.Name, S.Type, S.Cost 
FROM tbl_Source S
    INNER JOIN tbl_Rules R_N ON 
           (R_N.NameValues <> S.Name and R_N.NameOperator = 'NotIn' )
        OR (R_N.NameValues = S.Name and R_N.NameOperator = 'In' )
    INNER JOIN tbl_Rules R_S ON 
           R_S.RuleID = R_N.RuleID AND
           (R_S.TypeValue <> S.Type and R_S.TypeOperator = 'NotIn' )
        OR (R_S.TypeValue = S.Type and R_S.TypeOperator = 'In' )
GROUP BY 
    S.Name, S.Type, S.Cost
HAVING
    MAX(R_N.NameOperator) = MIN(R_N.NameOperator) 
    AND MAX(R_S.TypeOperator) = MIN(R_S.TypeOperator) 

结果：

RuleID      Name                 Type        Cost
----------- -------------------- ----------- -----------
1           Ann                  2           700
1           Cole                 2           200
2           Mike                 1           500
2           Mike                 1           800