时间戳

首页 > 解决方案 > 使用 Postgres 进行“多个”搜索

sql - 使用 Postgres 进行“多个”搜索

问题描述

基本上，我试图从一个表中获取一个值，然后在其他 3 个表中搜索该值。但我想在一个查询中完成这一切。

select user_id from users where email = 'bob@example.com

该搜索的值需要进入如下查询：

SELECT *
FROM table1
JOIN table2 ON (table1.user_id = 
table2.user_id)
WHERE table1.user_id  =  <<<THE RESULTS FROM THE FIRST QUERY>>>
OR table2.user_id = <<<THE RESULTS FROM THE FIRST QUERY>>>

如果 user_id 不在第一个表中，这个 JOIN 仍然有效吗？

标签： sqlpostgresql

解决方案

CTE -公共表表达式（AFAIK 始终在 Postgres 中具体化）将用作您的第一个查询的占位符，然后您可以使用它来加入其他表。此外，aUNION听起来像您想要的OR在一个或多个表中查找匹配数据的样式t1 .. t3，例如：

WITH cteUsers AS
(
    select user_id from users where email = 'bob@example.com'
)
SELECT t1.user_id, t1.othercol, ...
FROM table1 t1 INNER JOIN cteUsers cte on t1.user_id = cte.user_id
UNION
SELECT t2.user_id, t2.othercol, ...
FROM table1 t2 INNER JOIN cteUsers cte on t2.user_id = cte.user_id
UNION
SELECT t3.user_id, t3.othercol, ...
FROM table1 t3 INNER JOIN cteUsers cte on t3.user_id = cte.user_id;

附注：

  • othercol各个表的列中返回的数字和类型t1..t3必须匹配。
  • 如果同一个用户在多个表中匹配到相同的othercol值，那么UNION就会产生去重复的效果（类似DISTINCT）。UNION ALL如果您想要重复的行，请将其替换为。
  • 如果多个表t1 .. t3成功匹配连接到users，那么这将返回每个匹配的表一行（除非它被不同的删除，如上所述）
  • 如果表 1、2 或 3 中的任何一个都没有匹配项，则查询将不返回任何内容。如果您希望从不users考虑匹配 ( with nulls for unmatched columns) 的情况下返回单行，则需要LEFT JOIN用户和至少一个表 t1..t3 之间的 a

编辑 - 回复：确保users一行始终返回至少一条记录/指示匹配哪些表

如上所述，您可以使用 aLEFT OUTER JOIN来处理 3 个表中的任何一个都没有匹配项的情况。在这里，我已经将 3 个表匹配的输出汇总到另一个 CTE 中，然后在 CTE 之间执行最后一个 LOJ，并coalesce突出显示连接失败的位置（null如果需要，您显然也可以保留它）：

WITH cteUsers AS
(
    -- Replace with bob to see a match in 2 tables
    -- Replace with fred to see a match in 1 table.
    select user_id from users where email = 'missing@example.com' 
),
cteTables AS
(
  SELECT t1.user_id, 'MatchedTable1' as match, t1.othercol
  FROM table1 t1 INNER JOIN cteUsers cte on t1.user_id = cte.user_id
  UNION
  SELECT t2.user_id, 'MatchedTable2' as match, t2.othercol
  FROM table2 t2 INNER JOIN cteUsers cte on t2.user_id = cte.user_id
  UNION
  SELECT t3.user_id, 'MatchedTable3' as match, t3.othercol
  FROM table3 t3 INNER JOIN cteUsers cte on t3.user_id = cte.user_id
)
SELECT u.user_id, coalesce(match, 'Not Matched At All') as matched, t.othercol
FROM cteUsers u LEFT OUTER JOIN cteTables t ON u.user_id = t.user_id;

我在这里放了一个SqlFiddle，希望能把事情弄清楚？

推荐阅读