如何避免对 IN 子句重复此子查询? [英] How do I avoid repeating this subquery for the IN clause?

问题描述

我有一个 SQL 脚本(目前针对 SQLite 运行，但它可能适用于任何数据库引擎)两次使用相同的子查询，因为它可能会获取大量记录(该表有几百万行) 我只想调用一次.

I have an SQL script (currently running against SQLite, but it should probably work against any DB engine) that uses the same subquery twice, and since it might be fetching a lot of records (the table has a couple of million rows) I'd like to only call it once.

查询的缩短伪版本如下所示:

A shortened pseudo-version of the query looks like this:

SELECT * FROM
    ([the subquery, returns a column of ids]) AS sq
[a couple of joins, that fetches things from other tables based on the ids]
WHERE thisorthat NOT IN ([the subquery again])

我尝试以各种方式(带/不带括号，带/不带命名 sq 列等)使用名称 (sq)，但无济于事.

I tried just using the name (sq) in various ways (with/without parenthesis, with/without naming the column of sq etc) but to no avail.

我真的有重复这个子查询吗?

Do I really have to repeat this subquery?

说明:我在 python 和 sqlite 中做这个作为 可以 的一个小演示，但我希望我的解决方案在尽可能少的修改的情况下尽可能地扩展.在实际情况下，数据库将有几百万行，但在我的示例中，只有 10 行带有虚拟数据.因此，可以在 MySQL 上进行很好优化的代码 绝对足够好 - 它不必专门针对 SQLite 进行优化.但正如我所说，需要的修改越少越好.

Clarification: I am doing this in python and sqlite as a small demo of what can be done, but I would like my solution to scale as well as possible with as little modification as possible. In the real situation, the database will have a couple of million rows, but in my example there is just 10 rows with dummy data. Thus, code that would be well optimized on for example MySQL is absolutely good enough - it doesn't have to be optimized specifically for SQLite. But as I said, the less modification needed, the better.

推荐答案

在标准 SQL 中有一个 WITH 子句，但是，我不知道它是否被 SQLlite 支持 - 虽然当然值得一试:

There is a WITH clause in standard SQL, however, I don't know if it is supported by SQLlite - though of course worth a try:

WITH mySubQuery AS
(
  [the subquery code]
)

SELECT * FROM
    mySubQuery AS sq
    [a couple of joins, that fetches things from other tables based on the ids]
WHERE thisorthat NOT IN (mySubQuery)

也就是说，对于任何超过几千行的数据集，您在此处执行的操作可能会非常缓慢，因此如果可能，我会尝试对其进行改造 - NOT IN 应该是一般避免，特别是如果你也有几个连接.

That said, what you do here will likely be horribly slow for any data set that is more than a few thousand rows, so I'd try to remodel it if possible - NOT IN should be avoided in general, especially if you also have a couple of joins.

这篇关于如何避免对 IN 子句重复此子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！