在PostgreSQL中合并两个SELECT查询 [英] Combine two SELECT queries in PostgreSQL

问题描述

我想将两个选择查询与 UNION 组合。

如何使用第一个 SELECT的结果在第二个 SELECT 吗？

I would like to combine two select queries with UNION.
How can I use the result from the first SELECT in the second SELECT?

(SELECT carto_id_key FROM table1
    WHERE tag_id = 16)
UNION 
(SELECT * FROM table2
    WHERE carto_id_key = <the carto_id result from above> )

推荐答案

使用 CTE 可以在多个 SELECT 。

为此，您需要PostgreSQL 8.4 +：

Use a CTE to reuse the result from a subquery in more than one SELECT.
You need PostgreSQL 8.4+ for that:

WITH x AS (SELECT carto_id_key FROM table1 WHERE tag_id = 16)

SELECT carto_id_key
FROM   x

UNION ALL
SELECT t2.some_other_id_key
FROM   x
JOIN   table2 t2 ON t2.carto_id_key = x.carto_id_key

您最有可能想要 UNION ALL 代替 UNION 。不排除重复项，这样可以更快。

You most probably want UNION ALL instead of UNION. Doesn't exclude duplicates and is faster this way.

这篇关于在PostgreSQL中合并两个SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！