通過在數組中添加結果來重用 SELECT 查詢？

我寫了一個 PostgreSQL 函式，它返回一個特定的產品順序。現在我想，不僅要顯示，還要將第一個SELECT查詢的結果放到一個數組中，這樣我就可以在另一個選擇查詢中重用 ID。我首先嘗試為選擇查詢添加一個別名，例如在第二個查詢的語句中SELECT * FROM (SELECT id FROM products) as pr使用，但這不起作用……pr``NOT IN(pr)

我將通過一個例子更清楚地解釋它，這是該函式的簡化版本：

CREATE OR REPLACE FUNCTION featured_products(
   valid_to_in timestamp without time zone,
   taxonomy_id_in integer,
   product_limit_in integer)
 RETURNS SETOF integer AS
$BODY$
 BEGIN
   RETURN QUERY

   (
   -- #1
   SELECT * FROM (
       SELECT "product"."supplier_id" FROM products AS "product"
   ) AS "featured"
   LIMIT 2
   )

   UNION ALL
   SELECT *
       FROM (
           SELECT "product"."supplier_id" FROM products AS "product"
       ) AS "featured"
       WHERE id NOT IN (
           -- #2
           SELECT * FROM (
               SELECT "product"."supplier_id" FROM products AS "product"
           ) AS "featured"
           LIMIT 2
       )
   LIMIT product_limit_in;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE;

我刪除了一些連接和GROUP BY和ORDER BY語句，因此該函式更具可讀性。我在上面的程式碼中添加了#1和#2，所以你知道我對選擇查詢 1 和 2 的意思。

如您所見，查詢#2 應該返回與查詢#1 相同的結果。實際上，這些查詢要大得多。所以你我只想用一個 ID 數組替換第二個相同的查詢。更少的程式碼，可能更快。

我不知道如何將從第一個查詢返回的 ID 添加到數組中，並將其放入NOT IN(<id's>)語句而不是第二個查詢中。

有誰知道如何解決這個問題？

**這是CTE**的教科書案例，就像@Daniel 評論的那樣。

這個例子可以再簡化一些。您需要了解查詢LIMIT中的工作方式。UNION

CREATE OR REPLACE FUNCTION featured_products(valid_to_in      timestamp
                                          , taxonomy_id_in   integer
                                          , product_limit_in integer)
 RETURNS SETOF integer AS
$func$
BEGIN
  RETURN QUERY
  **WITH featured AS (SELECT supplier_id FROM products LIMIT 2)**
  SELECT supplier_id
  FROM   featured

  UNION ALL
  **(**
  SELECT p.supplier_id
  FROM   products p
  LEFT   JOIN featured f USING (supplier_id)
  WHERE  f.supplier_id IS NULL
  LIMIT  product_limit_in
  **)**  -- parens required - or not?
END
$func$  LANGUAGE plpgsql VOLATILE;

• LIMIT只能在UNION( ALL) 查詢中應用一次，除非您將查詢的分支括在括號中。您可能想要也可能不想添加括號。

  • 我有它的方式，除了來自 CTE 的“特色”行 之外，最多product_limit_in返回行。
  • 如果刪除括號，您將獲得最多的product_limit_in總行數- 這意味著即使是“特色”產品也可能被丟棄。

  相關：優化兩個大表上的查詢

• 無論哪種方式，如果可以避免，請不要**ORDER BY在您之前出現外部（組合）結果。LIMITPostgres 可以非常有效地優化查詢，一旦返回足夠多的行就停止評估（可能從匹配索引的頂部獲取元組）。這將不再可能，這可能會對性能產生巨大**影響。

• 用於從第二個SELECTLEFT JOIN / NOT NULL中排除特徵行，這可能比處理 NULL 值或空結果時更快NOT IN並且不會帶來“驚喜”。

  • 選擇其他表中不存在的行

• p.supplier_id在 Postgres（相對於其他一些 RDBMS）中，您可以f.supplier_id在加入USING (supplier_id).

是的，CTE 只評估一次：

查詢的一個有用屬性是每次執行父查詢時WITH它們只被評估一次 ，即使它們被父查詢或同級WITH查詢多次引用。

大膽強調我的。

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