如何在表达式中对另一个结果列重用结果列

就像这样:

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

您可以这样重用查询:

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

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

也许sql“with”子句会有帮助,如下所示 http://orafaq.com/node/1879 (其他数据库,如Postgres也这样做,不仅仅是oracle)。

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

实际上,我在这方面做了很多工作,并撞上了许多砖墙,但最终找到了一个答案-更多的黑客-但它工作得非常好,并将我的查询的读取开销减少了90%。。。。

因此,与其多次重复相关查询以从子查询检索多个列,我只使用concat将所有要返回的值转换为逗号分隔的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

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

我认为以下措施会奏效:

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

不是在动物身上测试的-你会是第一个!:-)

分享和享受。

使用交叉涂抹或外涂。

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

这是一个相当古老的问题,但我遇到了这个问题,并看到了这篇文章,但没有设法解决我的问题,使用给定的答案,所以我最终得到了这个解决方案:

如果您的查询是:

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转换为同一子查询的2x。

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

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