我有一个类似这样的查询:
select
(price1 + price2 + price3) as total_price
from prices
我该如何使用计算列total_price来计算其他总价呢?
select
(price1 + price2 + price3) as total_price,
(price4 + total_price) as total_price2
from prices
这是否可能?
我有一个类似这样的查询:
select
(price1 + price2 + price3) as total_price
from prices
select
(price1 + price2 + price3) as total_price,
(price4 + total_price) as total_price2
from prices
这是否可能?
不可能在同一级别引用列别名。出现在相同逻辑查询处理阶段中的表达式被视为同时评估。
在SQL中,事情是“同时发生”的,而不是像顺序文件/过程语言模型那样“从左到右”
但是你可以在CTE中定义它,然后在CTE之外重复使用它。
示例
WITH T
AS (SELECT ( price1 + price2 + price3 ) AS total_price,
price4
FROM prices)
SELECT total_price,
( price4 + total_price ) AS total_price2
FROM T
ALTER TABLE prices ADD
total_price AS (price1 + price2 + price3)
select
total_price,
(price4 + total_price) as total_price2
from prices
这样,您就可以应用DRY原则...
select T.total_price,
P.price4 + T.total_price as total_price2
from prices as P
cross apply (select P.price1 + P.price2 + P.price3) as T(total_price)