将select查询的结果多次用于另一个查询

Question

我有一个复杂的select查询(Query-1)，它是使用INTERSECT执行的，它返回特定列的it。

查询#1：

SELECT my_id FROM my_table
INTERSECT
SELECT my_id FROM other_table;

现在有了另一个更复杂的查询，它多次需要查询#1的结果。

查询#2：

SELECT * 
FROM
    (SELECT my_id, col_1, my_value
     FROM my_table
     WHERE my_id IN (result from query-1) 
       AND col_3 IN (SELECT col_3 FROM another_table1 
                     WHERE my_id IN (result from query-1) 
                       AND another_col IN (SELECT another_col 
                                           FROM another_table2 
                                           WHERE my_id IN (result from query-1))))
    PIVOT 
        (MIN(my_value)
            FOR(col_1) IN(1 AS name, 2 AS lastname, 3 AS address)
        )

正如您可以看到query-1的结果在query-2中被多次需要，我尝试的是在query-2中任何需要的地方替换整个query-1，这增加了查询的复杂性和可读性。

有没有一种简单的方法可以做到这一点？

Answer 1

如何使用with子句(子查询因子句)：

with query-1 as (SELECT my_id FROM my_table
INTERSECT
SELECT my_id FROM other_table)

SELECT * FROM
(
   SELECT my_id, col_1, my_value
   FROM my_table
   WHERE my_id IN (select id from query-1) AND col_3 IN
      (SELECT col_3 FROM another_table1 WHERE my_id IN (select id from query-1) AND another_col IN
         (SELECT another_col FROM another_table2 WHERE my_id IN (select id from query-1))
)
)
PIVOT (
   MIN(my_value)
   FOR(col_1)
   IN(1 AS name, 2 AS lastname, 3 AS address)
)

Answer 2

我绝对会对你会多次使用的任何查询使用View。

我将首先编写以下代码：

Create View Query1
as
SELECT my_id FROM my_table
INTERSECT
SELECT my_id FROM other_table;

一旦我选择了上面的代码，我将执行(F5)它。这将存储视图。然后，我将编写下面的代码，并在您想调用它的时候执行：

Select * From Query1

或者，您可以简单地在Query2中使用Query1。