如何将表达式中的结果列重用于另一个结果列

Question

示例：

SELECT
   (SELECT SUM(...) FROM ...) as turnover,
   (SELECT SUM(...) FROM ...) as cost,
   turnover - cost as profit

当然这是无效的（至少在 Postgres 中），但是如何在查询中实现相同的效果而不需要重写子查询两次？

Answer 1

就像这样：

SELECT
   turnover,
   cost,
   turnover - cost as profit
from (
   (SELECT SUM(...) FROM ...) as turnover,
   (SELECT SUM(...) FROM ...) as cost
   ) as partial_sums

Answer 2

您可以像这样重复使用查询：

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

这相当于：

SELECT *
FROM(
 SELECT
   TURNOVER.sum as SUM_TURNOVER
 FROM
 (
   SELECT SUM(...) FROM ...)
 )AS TURNOVER,
 (
   SELECT SUM(...) FROM ...
 )AS COST
 WHERE ....
) AS a

这里有一点需要注意。第一种方法更具可读性和可重用性，但第二种方法可能更快，因为数据库可能会为其选择更好的计划。

Answer 3

也许sql“with”子句可以提供帮助，如此处所示 http://orafaq.com/node/1879 （其他数据库如 Postgres 也这样做，不仅仅是 oracle）。

Answer 4

SELECT turnover, cost, turnover - cost
FROM
(
SELECT
(SELECT ...) as turnover,
(SELECT ...) as cost
) as Temp

Answer 5

实际上，我在这方面做了很多工作，也遇到了很多困难，但最终找到了一个答案 - 更多的是一个黑客 - 但它工作得很好，并将我的查询的读取开销减少了 90%

....与多次复制相关查询以从子查询中检索多个列相比，我只是使用将所有想要返回的值连接到逗号分隔的 varchar 中，然后在应用程序中再次展开它们...

所以

select a,b,
(select x from bigcorrelatedsubquery) as x,
(select y from bigcorrelatedsubquery) as y,
(select z from bigcorrelatedsubquery) as z
from outertable

我现在不是

select a,b,
(select convert(varchar,x)+','+convert(varchar,x)+','+convert(varchar,x)+',' 
from bigcorrelatedsubquery) from bigcorrelatedquery) as xyz
from outertable
group by country

这样做现在拥有我需要的所有三个相关“标量”值，但只需执行一次相关子查询而不是三次。

Answer 6

我认为以下方法会起作用：

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);

未在动物身上进行测试 - 你将是第一个！ :-)

分享并享受。

Answer 7

使用交叉涂抹或外部涂抹。

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

Answer 8

这已经很老了，但我遇到了这个问题并看到了这篇文章，但没有设法使用给定的答案解决我的问题，所以我最终找到了这个解决方案：

如果您的查询是：

SELECT
   (SELECT SUM(...) FROM ...) as turnover,
   (SELECT SUM(...) FROM ...) as cost,
   turnover - cost as profit

您可以将其转换为子查询，然后使用字段例如：

SELECT *,(myFields.turnover-myFields.cost) as profit 
FROM
(      
SELECT
       (SELECT SUM(...) FROM ...) as turnover,
       (SELECT SUM(...) FROM ...) as cost

) as myFields

我不完全确定这是否是一种不好的做法，但从性能角度来看，查询超过 224,000 条记录需要 1.5 秒，这对我来说似乎没问题
不确定它后来是否会变成 DB 的同一子查询的 2 倍。

Answer 9

您可以使用这样的用户定义变量

SELECT
   @turnover := (SELECT SUM(...) FROM ...),
   @cost := (SELECT SUM(...) FROM ...),
   @turnover - @cost as profit

http://dev.mysql .com/doc/refman/5.7/en/user-variables.html