主页 / dba / 问题 / 253247
Accepted
Jana
Asked: 2019-11-14 23:53:31 +0800 CST

如何避免 WHERE 子句中出现大量 LIKE 比较的较低（列）？

  • 772

我必须过滤字符串列中的数据，因此需要将使用的列NOT LIKE与一堆字符串进行比较。

我正在使用 SQL Server，我的代码如下所示：

SELECT history.date,
       history.new_value,
       res.resource_name
FROM   item_history history, 
       owned_resource   res
WHERE  (history.attr_name = 'resource_contact' OR history.attr_name = 'Kontakt-UUID')
       AND res.inactive = 0 
       AND history.com_par_id = res.own_resource_uuid
       AND lower(history.new_value) NOT LIKE '%tp für%'
       AND lower(history.new_value) NOT LIKE '%rücklieferung%'
       AND lower(history.new_value) NOT LIKE '%rückläufer%'
       AND lower(history.new_value) NOT LIKE '%stoerreserve%'
       AND lower(history.new_value) NOT LIKE '%zentrallager%'
       AND lower(history.new_value) NOT LIKE '%bhs-pool%'
       AND lower(history.new_value) NOT LIKE '%lager halle%'        
       AND lower(history.new_value) NOT LIKE '%lager logistik%'
       AND lower(history.new_value) NOT LIKE 'reserve %'
       AND lower(history.new_value) NOT LIKE '%igeko%bhs%'
       AND lower(history.new_value) NOT LIKE '%service%ecg%'
       AND lower(history.new_value) NOT LIKE '%multifunktionsdrucker%'
       AND lower(history.new_value) NOT LIKE 'nn%gisa%raum%'
       AND lower(history.new_value) NOT LIKE '%citrix%admins%'
       AND lower(history.new_value) NOT LIKE '%personalwesen%'
       AND lower(history.new_value) NOT LIKE '%etagendrucker%'
       AND lower(history.new_value) NOT LIKE '%schulungsraum%'
       AND lower(history.new_value) NOT LIKE '%team%raum%'
       AND lower(history.new_value) NOT LIKE  '%beratungsraum%'
       AND lower(history.new_value) != 'reserve'
)

我猜性能并不是一遍又一遍地调用“lower()”的最佳方法。另外，作为一名程序员，看到这么多冗余代码，我的指甲都卷起来了。不幸的是，我没有找到使用变量或其他东西的好方法。（我想补充一点，我不能简单地添加一个新的计算列，这将是一个好方法，我只被授权读取数据。）

有什么建议可以让代码更智能吗？

sql-server where

3 个回答

  1. Best Answer

    我会在这里使用join而不是旧的写作方式
    关于“lower”，你确定这是需要的吗？
    你检查新值的排序规则了吗？
    如果它在排序规则名称中有 CI（不区分大小写），则可以避免使用 lower。

    如果你这样做，你可以写这样的东西

    WITH cte
    AS ( SELECT history.date
               ,history.new_value
               ,res.resource_name
               ,LOWER(history.new_value) AS new_value
         FROM   item_history history
         JOIN   owned_resource res
                ON (   history.attr_name = 'resource_contact'
                       OR history.attr_name = 'Kontakt-UUID' )
                   AND res.inactive = 0
                   AND history.com_par_id = res.own_resource_uuid )
SELECT *
FROM   cte
WHERE  new_value NOT LIKE '%tp für%'
       AND new_value NOT LIKE '%rücklieferung%'
       AND new_value NOT LIKE '%rückläufer%'
       AND new_value NOT LIKE '%stoerreserve%'
       AND new_value NOT LIKE '%zentrallager%'
       AND new_value NOT LIKE '%bhs-pool%'
       AND new_value NOT LIKE '%lager halle%'
       AND new_value NOT LIKE '%lager logistik%'
       AND new_value NOT LIKE 'reserve %'
       AND new_value NOT LIKE '%igeko%bhs%'
       AND new_value NOT LIKE '%service%ecg%'
       AND new_value NOT LIKE '%multifunktionsdrucker%'
       AND new_value NOT LIKE 'nn%gisa%raum%'
       AND new_value NOT LIKE '%citrix%admins%'
       AND new_value NOT LIKE '%personalwesen%'
       AND new_value NOT LIKE '%etagendrucker%'
       AND new_value NOT LIKE '%schulungsraum%'
       AND new_value NOT LIKE '%team%raum%'
       AND new_value NOT LIKE '%beratungsraum%'
       AND new_value != 'reserve';
    • 3

  2. 为了完整起见，您也可以通过以下方式解决问题（许多排除）：

    SELECT history.date
     , history.new_value
     , res.resource_name
  FROM item_history        AS history
 INNER JOIN owned_resource AS res
    ON history.com_par_id = res.own_resource_uuid
   AND res.inactive       = 0
 WHERE (history.attr_name = 'resource_contact' OR history.attr_name = 'Kontakt-UUID')
   AND NOT EXISTS
     (SELECT *
        FROM (VALUES ('%tp für%')
                   , ('%rücklieferung%')
                   , ('%rückläufer%')
                   , ('%stoerreserve%')
                   , ('%zentrallager%')
                   , ('%bhs-pool%')
                   , ('%lager halle%')
                   , ('%lager logistik%')
                   , ('reserve %')
                   , ('%igeko%bhs%')
                   , ('%service%ecg%')
                   , ('%multifunktionsdrucker%')
                   , ('nn%gisa%raum%')
                   , ('%citrix%admins%')
                   , ('%personalwesen%')
                   , ('%etagendrucker%')
                   , ('%schulungsraum%')
                   , ('%team%raum%')
                   , ('%beratungsraum%')
                   , ('reserve')) AS ex (exclude)
       WHERE history.new_value LIKE ex.exclude);

    PS：我不喜欢旧的 oracle-school 和非 ANSI 标准的逗号样式 JOIN...

    • 1

  3. 根据我的经验，在 WHERE 条件下使用 SQL 函数对 SQL 性能不利。

    您可以重写脚本以执行以下操作：

    1. 创建临时表或变量表
    2. INSERT INTO SELECT 到该临时表或变量表，但在 SELECT 中使用 lower() 到您当前在 WHERE 条件下使用的列。
    3. 从临时表或变量表中选择 [列]。
    • 0

相关问题