在单个查询中多次使用相同的子查询 [英] Same sub-query used multiple times in a single query

问题描述

我正在运行一个查询，其中包含在 WHERE 子句中多次使用的 相同 子查询.

I am running a query that contains same sub-query used multiple times in a WHERE clause.

我有一个包含两个字段 client_id、buyer_id 的表格.

I have a table having tow fields client_id, buyer_id.

子查询返回要从结果中排除的日期列表.

The sub-query returns the list of dates to be excluded from the result.

这就是我使用它的方式.

This is how I am using it.

SELECT
  id, client_id, buyer_id
FROM relation
WHERE
  client_id NOT IN (SELECT <some_id> FROM <some_table> WHERE ...)
  AND buyer_id NOT IN (SELECT <some_ids> FROM <some_table> WHERE ...)

这按预期工作，但令我烦恼的是有两个相同的子查询.不知道有没有什么办法可以一次用完，结果两处都用.

This is working as expected but what bothers me that there are two same sub-queries. I wonder if there is a way that I can use it once and use the result for both places.

谢谢.

推荐答案

您可以使用 NOT EXISTS 编写:

SELECT
    id, client_id, buyer_id
FROM relation AS r
WHERE NOT EXISTS
      ( SELECT 1 
        FROM <some_table> 
        WHERE (r.client_id = <some_id> OR r.buyer_id = <some_id>) 
          AND ...
      ) ;

这篇关于在单个查询中多次使用相同的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！