SQL 如何将表达式中的结果列重用于另一个结果列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4220587/
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
How to reuse a result column in an expression for another result column
提问by Wernight
Example:
例子：
SELECT
(SELECT SUM(...) FROM ...) as turnover,
(SELECT SUM(...) FROM ...) as cost,
turnover - cost as profit
Sure this is invalid (at least in Postgres) but how to achieve the same in a query without rewriting the sub-query twice?
当然这是无效的（至少在 Postgres 中）但是如何在不重写子查询两次的情况下在查询中实现相同的效果？
采纳答案by Denis de Bernardy
Like so:
像这样：
SELECT
turnover,
cost,
turnover - cost as profit
from (
(SELECT SUM(...) FROM ...) as turnover,
(SELECT SUM(...) FROM ...) as cost
) as partial_sums
回答by Alex Calugarescu
You could reuse the query like this:
您可以像这样重用查询：
WITH
TURNOVER AS (
SELECT SUM(...) FROM ...)
),
COST AS(
SELECT SUM(...) FROM ...
)
SELECT *
FROM(
SELECT
TURNOVER.sum as SUM_TURNOVER
FROM
TURNOVER,COST
WHERE ....
) AS a
This is equivalent to :
这相当于：
SELECT *
FROM(
SELECT
TURNOVER.sum as SUM_TURNOVER
FROM
(
SELECT SUM(...) FROM ...)
)AS TURNOVER,
(
SELECT SUM(...) FROM ...
)AS COST
WHERE ....
) AS a
There is a point to note here. The first method is more readable and reusable, but the second method might be faster, because the DB might choose a better plan for it.
这里有一点需要注意。第一种方法更具可读性和可重用性，但第二种方法可能更快，因为 DB 可能会为其选择更好的计划。
回答by andrers52
Perhaps the sql "with" clause could help, as presented here http://orafaq.com/node/1879(other databases such as Postgres do it as well, not just oracle).
也许 sql "with" 子句会有所帮助，如http://orafaq.com/node/1879 所示（其他数据库如 Postgres 也可以这样做，而不仅仅是 oracle）。
回答by Eric.K.Yung
SELECT turnover, cost, turnover - cost
FROM
(
SELECT
(SELECT ...) as turnover,
(SELECT ...) as cost
) as Temp
回答by bhealy
Actually I did a lot of work on this, and hit many brick walls, but finally figured out an answer - more of a hack - but it worked very well and reduced the read overhead of my queries by 90%....
实际上我在这方面做了很多工作，并撞到了很多砖墙，但最终找到了一个答案——更像是一个黑客——但它工作得很好，并将我的查询的读取开销减少了 90%....
So rather than duplicating the correlated query many times to retrieve multiple columns from the subquery, I just used concat all the values I want to return into a comma separated varchar, and then unroll them again in the application...
因此，我没有多次复制相关查询以从子查询中检索多个列，而是将所有要返回的值连接到逗号分隔的 varchar 中，然后在应用程序中再次展开它们...
So instead of
所以代替
select a,b,
(select x from bigcorrelatedsubquery) as x,
(select y from bigcorrelatedsubquery) as y,
(select z from bigcorrelatedsubquery) as z
from outertable
I now do
我现在做
select a,b,
(select convert(varchar,x)+','+convert(varchar,x)+','+convert(varchar,x)+','
from bigcorrelatedsubquery) from bigcorrelatedquery) as xyz
from outertable
group by country
I now have all three correlated 'scalar' values I needed but only had to execute the correlated subquery once instead of three times.
我现在拥有我需要的所有三个相关“标量”值，但只需执行一次相关子查询而不是三次。
回答by Bob Jarvis - Reinstate Monica
I think the following will work:
我认为以下将起作用：
SELECT turnover, cost, turnover-cost as profit FROM
(SELECT 1 AS FAKE_KEY, SUM(a_field) AS TURNOVER FROM some_table) a
INNER JOIN
(SELECT 1 AS FAKE_KEY, SUM(a_nother_field) AS COST FROM some_other_table) b
USING (FAKE_KEY);
Not tested on animals - you'll be first! :-)
未在动物身上进行测试 - 您将是第一个！:-)
Share and enjoy.
分享和享受。
回答by William Egge
Use a cross apply or outer apply.
使用交叉应用或外部应用。
SELECT
Calc1.turnover,
Calc2.cost,
Calc3.profit
from
cross apply ((SELECT SUM(...) as turnover FROM ...)) as Calc1
cross apply ((SELECT SUM(...) as cost FROM ...)) as Calc2
/*
Note there is no from Clause in Calc 3 below.
This is how you can "stack" formulas like in excel.
You can return any number of columns, not just one.
*/
cross apply (select Calc1.turnover - Calc2.cost as profit) as Calc3
回答by Niklas
this is pretty old but i ran into this problem and saw this post but didnt manage to solve my problem using the given answers so i eventually arrived at this solution :
这已经很老了，但我遇到了这个问题并看到了这篇文章，但没有设法使用给定的答案解决我的问题，所以我最终找到了这个解决方案：
if your query is :
如果您的查询是：
SELECT
(SELECT SUM(...) FROM ...) as turnover,
(SELECT SUM(...) FROM ...) as cost,
turnover - cost as profit
you can turn it into a subquery and then use the fields such as :
您可以将其转换为子查询，然后使用以下字段：
SELECT *,(myFields.turnover-myFields.cost) as profit
FROM
(
SELECT
(SELECT SUM(...) FROM ...) as turnover,
(SELECT SUM(...) FROM ...) as cost
) as myFields
i'm not entirely sure if this is a bad way of doing things but performance wise it seems okay for me querying over 224,000records took 1.5 sec
not sure if its later on turned into 2x of the same sub query by DB.
我不完全确定这是否是一种糟糕的做事方式，但从性能上看，我查询224,000记录需要 1.5 秒，不确定它后来是否变成了 DB 相同子查询的 2 倍。
回答by Dinesh
You can use user defined variables like this
您可以像这样使用用户定义的变量
SELECT
@turnover := (SELECT SUM(...) FROM ...),
@cost := (SELECT SUM(...) FROM ...),
@turnover - @cost as profit
