在where子句中使用计算字段

Question

有没有办法在where语句中使用计算字段？

我想做这样的事情：

SELECT a, b, a+b as TOTAL FROM (
   select 7 as a, 8 as b FROM DUAL
   UNION ALL
   select 8 as a, 8 as b FROM DUAL
   UNION ALL
   select 0 as a, 0 as b FROM DUAL
)
WHERE TOTAL <> 0
;

但我收到了 ORA-00904: "TOTAL": 无效的标识符 错误。

所以我必须使用

SELECT a, b, a+b as TOTAL FROM (
   select 7 as a, 8 as b FROM DUAL
   UNION ALL
   select 8 as a, 8 as b FROM DUAL
   UNION ALL
   select 0 as a, 0 as b FROM DUAL
)
WHERE a+b <> 0
;

- Luc M

Answer 1

从逻辑上讲，select子句是查询评估的最后一部分之一，因此别名和派生列不可用（除了order by，它在逻辑上最后执行）。

使用派生表是解决这个问题的一种方法：

select * 
from (SELECT a, b, a+b as TOTAL FROM ( 
           select 7 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 8 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 0 as a, 0 as b FROM DUAL) 
    )
WHERE TOTAL <> 0 
; 

- Shannon Severance

---

我在尝试使用存储过程时，上述语法无法工作，除非我也给派生表起别名。 - DilbertDave

@DilbertDave，有趣。你是在Oracle中尝试吗？如果我没记错的话，SQL Server总是需要为派生表命名别名。 - Shannon Severance

OP询问关于Oracle不需要别名的问题。我很高兴这个回答能够帮助到您，尽管它是针对另一个平台的。 - Shannon Severance

啊 - 刚刚注意到了 Oracle 的错误信息和标签 脸红。不过，它指引我朝着正确的方向，所以点个赞 :-) - DilbertDave

Answer 2

这将有效...

select * 
from (SELECT a, b, a+b as TOTAL FROM ( 
           select 7 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 8 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 0 as a, 0 as b FROM DUAL) 
    ) as Temp
WHERE TOTAL <> 0; 

- Anil

---

这个答案很有用，因为它使用了as Temp来命名内部查询。在SQL Server中，这一点非常重要以确保其正确执行。 - Mike Finch