應用一長串 LIKE 模式的最佳方法？

作為這個問題的後續，我有自己的一個。

最初的問題涉及使用CASE大於 100 個選項的語句，並且該語句必須在 4 個地方使用 - 所以顯然 SQL 很麻煩。OP 的問題涉及 SQL Server 2012，但是我的問題是關於 PostgreSQL。

在我的回答中，我建議使用 aVIEW作為“一站式”解決方案——即聲明VIEW一次，在任何地方使用它——這也適用於未來的任何查詢及其任何變體。

另一位發帖人 (@AndriyM) 建議使用 aCROSS APPLY來解決問題，這是另一種解決方案。PostgreSQL 語法是JOIN LATERAL

然後，我在原始答案中添加了 CTE（公用表表達式）作為另一種可能的解決方案。

因此，OP 現在有 5 個選項：

1. CASE
2. VIEW
3. JOIN LATERAL（CROSS APPLY對於 SQL 伺服器）
4. CTE
5. Separate table

我排除了更改基礎數據的選項，因為在這個論壇中，顧問/DBA/程序員經常不允許更改基礎數據 - 也使答案更有趣！

顯然，CASE具有 > 100 個選項 (x4) 的表達式非常繁瑣和復雜 - 但是什麼時候使用是個好主意CASE，在什麼時候它會變成減號而不是加號？

在我看來（不僅僅是因為這是我的答案！），aVIEW是最佳解決方案 - 它很簡單，適用於所有 RDBMS，並且是永久性的，並且如果 OP 希望修改查詢，它將適用於現在和將來的所有查詢.

該JOIN LATERAL構造也可以作為一種派生表工作，這幾乎就是 aCTE也是。它們都可以在同一個查詢中使用。

5 種方法中哪一種更好/最好，技術（易用性、速度、查詢計劃優化）在什麼時候傾向於特定解決方案？

我會在子查詢中使用翻譯表。展示（Postgres 10+）：LATERAL

CREATE TABLE ac_translate (
  ord_nr int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, like_pattern text NOT NULL
, target text NOT NULL
);

INSERT INTO ac_translate(like_pattern, target) VALUES 
  ('AIR NEW Z%'       , 'AIR NEW ZEALAND')  -- rows in order of precedence!
, ('AIR BP%'          , 'AIR BP')
, ('ADDICTION ADVICE%', 'ADDICTION ADVICE')
, ('AIA%'             , 'AIA')
;

看：

• Postgres 是否保留記錄的插入順序？

操縱ord_nr以調整優先級。

詢問：

SELECT **COALESCE(act.target, ac.accountName)** AS accountname
    , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
**LEFT JOIN LATERAL (
SELECT a1.target
FROM ac_translate a1
WHERE ac.accountname LIKE a1.like_pattern
ORDER BY a1.ord_nr
LIMIT 1
) act ON true**
GROUP BY **1**;

或者使用相關的子查詢：

SELECT COALESCE(
       (SELECT a1.target
        FROM   ac_translate a1
        WHERE  ac.accountname LIKE a1.like_pattern
        ORDER  BY a1.ord_nr
        LIMIT  1), ac.accountName) AS accountname
    , SUM(ac.charge_amount) AS sum_amount
FROM   account_code ac
GROUP BY 1;

這很容易處理，將一長串選項保留在程式碼之外，並將其放入可以正確維護的表中。並且速度適中。

我們不能輕易使用普通的LEFT JOIN ac_translate，因為CASE遍歷模式以返回單一的第一個匹配。我們不能僅僅加入一個集合，如果一個模式是另一個模式的前綴，它可能會返回多個匹配項，例如“AIR%”和“AIR N%”。所以我們使用翻譯表中的排序號來優先考慮子查詢中的匹配。

引用問題中的ELSE子句解析為原始值。這是在這裡實現的。基本上，這結合了前兩個答案的優點。COALESCE

最重要的是，我GROUP BY 1以另一種方式避免重複冗長的表達式（這裡實際上不再需要）。看：

• 將一列的多個結果行連接成一個，按另一列分組$$ duplicate $$

速度

由於 Postgres 被迫按順序遍歷所有行並評估LIKE表達式，因此性能會隨著轉換錶中的行數而下降。如果這還不夠快，我們需要index support，但表達式不是“sargable” - 我們需要索引的表達式在運算符的右側，並且沒有COMMUTATORfor LIKE。細節：

• PostgreSQL 可以索引數組列嗎？

不過，有一個解決方法。我的範例要求模式至少有 3 個前導字元（3是我的任意選擇）。在轉換錶中添加一個CHECK約束以強制執行此規則，並在前導三元組上添加一個表達式索引：

CREATE INDEX ac_translate_left_idx ON ac_translate (left(like_pattern, 3));

調整查詢：

SELECT COALESCE(act.target, ac.accountName) AS accountname
    , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
  SELECT a1.target
  FROM   ac_translate a1
  **WHERE left(ac.accountname, 3) = left(a1.like_pattern, 3)
AND**    ac.accountname LIKE a1.like_pattern
  ORDER  BY a1.ord_nr
  LIMIT  1
  ) act ON true
GROUP BY 1;

在翻譯表中有足夠的行（以及有利的估計和成本設置）後，Postgres 將使用非常快速的索引掃描將其縮小到少數候選者（如果有的話），並僅使用表達式過濾其餘部分。LIKE應該縮放就好了。我將EXPLAIN輸出添加到小提琴中作為概念證明：

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/206884