在这个特定的上下文中,有没有更有效的方法来编写我的过滤器?

j8ag8udp  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(264)

我正在使用 SQL Server 2014 我有一张table( t1 )在我的数据库中,它包含一个数字列表(n1到n6)。摘录如下:

Id   n1   n2   n3   n4   n5   n6
100  3    10   26   31   35   39
101  1    3    11   22   36   40
102  10   19   20   30   39   40
103  6    12   25   27   28   33
...

假设我想通过排除数字3和19所在的行来过滤掉这个表,我的过滤代码如下所示:

Select * from t1

WHERE [n1] not in (3,19)
AND [n2] not in (3,19)
AND [n3] not in (3,19)
AND [n4] not in (3,19)
AND [n5] not in (3,19)
AND [n6] not in (3,19)

预期产量:

Id   n1   n2   n3   n4   n5   n6
103  6    12   25   27   28   33
...

有没有更有效的方法来编写过滤器?

yc0p9oo0

yc0p9oo01#

一个选项使用 not exists 以及 values() :

select t.*
from mytable t
where not exists (
    select 1
    from (values(n1), (n2), (n3), (n4), (n5), (n6)) x(n)
    where n in (3, 19)
)

当列表中的列和/或值的数量增加时,这比原始查询的伸缩性更好—尽管这不一定更有效。
db小提琴演示:

Id | n1 | n2 | n3 | n4 | n5 | n6
--: | -: | -: | -: | -: | -: | -:
103 |  6 | 12 | 25 | 27 | 28 | 33
xyhw6mcr

xyhw6mcr2#

您的筛选器很好,但您可能会发现编写为:

where 3 not in (n1, n2, n3, n4, n5, n6) and
      19 not in (n1, n2, n3, n4, n5, n6)

因为引用的所有六列都是不等式,所以无法真正提高性能。您可以修复数据模型,使列位于单独的行中,从而允许使用索引。

thtygnil

thtygnil3#

将不需要的值集放入cte中,并使用except运算符
正如其他人所指出的,数据模型并不理想。
来解决这个问题 NOT IN 过滤效率低下,您可以像这样使用except操作符:

--sample data
   WITH smple ( id, n1, n2, n3, n4, n5, n6) AS
    ( SELECT 100, 3, 10, 26, 31, 35, 39
    union all
    select 101,1,3,11,22,36,40
    union all
    select
    102,10,19,20,30,39,40
    union all
    select
    103,6,12,25,27,28,33
    ),
--end sample data
    not_vals (n) as (select 3 union all select 19 ) 
    SELECT
        s.*
    FROM
        smple s
    EXCEPT 
    SELECT
        s.*
    FROM
        smple s join not_vals nv on
                nv.n IN ( s.n1, s.n2, s.n3, s.n4, s.n5, s.n6)
     ;

从性能的Angular 来看可以使用的各种方法的效率是很有趣的。
最终的解决方案应该具有良好的性能,因此相关子查询可能不会提供这种性能。
下面是一个相关子查询:

--sample data
with smple ( id, n1, n2, n3, n4, n5, n6) AS
( SELECT 100, 3, 10, 26, 31, 35, 39
union all
select 101,1,3,11,22,36,40
union all
select
102,10,19,20,30,39,40
union all
select
103,6,12,25,27,28,33
),
--end sample data
not_vals (n) as (select 3 union all select 19 ) 
SELECT
    s.*
FROM
    smple s
WHERE
    NOT EXISTS (
        SELECT 1 FROM not_vals nv
        WHERE
            nv.n IN ( s.n1, s.n2, s.n3, s.n4, s.n5, s.n6)
    )
;

相关问题