SQL - 在一个语句中使用查询结果作为其他两个查询的基础
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/444673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Use results of a query as basis for two other queries in one statement
提问by Claudiu
I'm doing a probability calculation. I have a query to calculate the total number of times an event occurs. From these events, I want to get the number of times a sub-event occurs. The query to get the total events is 25 lines long and I don't want to just copy + paste it twice.
我在做一个概率计算。我有一个查询来计算事件发生的总次数。从这些事件中，我想获得子事件发生的次数。获取总事件的查询有 25 行长，我不想只是复制 + 粘贴两次。
I want to do two things to this query: calculate the number of rows in it, and calculate the number of rows in the result of a query on this query. Right now, the only way I can think of doing that is this (replace @total@ with the complicated query to get all rows, and @conditions@ with the less-complicated conditions that rows, from @total@, must have to match the sub-event):
我想对这个查询做两件事：计算其中的行数，并计算这个查询的查询结果中的行数。现在，我能想到的唯一方法就是这样（用复杂的查询替换 @total@ 以获取所有行，用不太复杂的条件 @conditions@ 替换来自 @total@ 的行必须匹配子事件）：
SELECT (SELECT COUNT(*) FROM (@total@) AS t1 WHERE @conditions@) AS suboccurs,
COUNT(*) AS totaloccurs FROM (@total@) as t2
As you notice, @total@ is repeated twice. Is there any way around this? Is there a better way to do what I'm trying to do?
如您所见，@total@ 重复了两次。有没有办法解决？有没有更好的方法来做我想做的事情？
To re-emphasize: @conditions@ does depend on what @total@ returns (it does stuff like t1.foo = bar).
再次强调：@conditions@ 确实取决于@total@ 返回的内容（它会做类似的事情t1.foo = bar）。
Some final notes: @total@ by itself takes ~250ms. This more complicated query takes ~300ms, so postgres is likely doing some optimization, itself. Still, the query looks terribly ugly with @total@ literally pasted in twice.
一些最后的说明：@total@ 本身需要大约 250 毫秒。这个更复杂的查询需要大约 300 毫秒，因此 postgres 本身可能会进行一些优化。尽管如此，查询看起来非常难看，@total@ 字面上粘贴了两次。
采纳答案by Andru Luvisi
SELECT COUNT(*) as totaloccurs, COUNT(@conditions@) as suboccurs
FROM (@total@ as t1)
回答by EvilTeach
If your sql supports subquery factoring, then rewriting it using the WITH statement is an option. It allows subqueries to be used more than once. With will create them as either an inline-view or a temporary table in Oracle.
如果您的 sql 支持子查询分解，则可以选择使用 WITH 语句重写它。它允许多次使用子查询。With 会将它们创建为内联视图或 Oracle 中的临时表。
Here is a contrived example.
这是一个人为的例子。
WITH
x AS
(
SELECT this
FROM THERE
WHERE something is true
),
y AS
(
SELECT this-other-thing
FROM somewhereelse
WHERE something else is true
),
z AS
(
select count(*) k
FROM X
)
SELECT z.k, y.*, x.*
FROM x,y, z
WHERE X.abc = Y.abc
回答by Andru Luvisi
Put the reused sub-query into a temp table, then select what you need from the temp table.
将重用的子查询放入临时表中，然后从临时表中选择您需要的内容。
回答by Andru Luvisi
@EvilTeach:
@EvilTeach：
I've not seen the "with" (probably not implemented in Sybase :-(). I like it: does what you need in one chunk then goes away, with even less cruft than temp tables. Cool.
我还没有看到“with”（可能没有在 Sybase 中实现 :-(）。我喜欢它：在一个块中完成你需要的东西然后消失，甚至比临时表更少。很酷。
