如何使用计算列计算同一视图中的另一列

How to use a calculated column to calculate another column in the same view(如何使用计算列计算同一视图中的另一列)

问题描述

我希望你能帮助解决这个问题.我正在使用 Oracle SQL(此视图的 SQL Developer)...

I am hoping you can help with this question. I am using Oracle SQL (SQL Developer for this view)...

如果我有一个包含以下列的表格:

If I have a table with the following columns:

• A 列(数字)
• B 列(数字)
• C 列(编号)

在我看来我有

Select  
ColumnA,
ColumnB,
ColumnA + ColumnB As calccolumn1

现在，我想使用 calccolumn1但我不能只说...

Now at this point, I want to use calccolumn1 but I cannot just say...

Select  
ColumnA,
ColumnB,
ColumnA + ColumnB As calccolumn1
calccolumn1 / ColumnC as calccolumn2

我假设我需要某种类型的子查询......但这就是我需要你帮助的地方......我将如何表述查询以便我可以在同一查询的另一个计算中使用 calccolumn1?它可能是 If then 或 Case when，但最重要的是它是某个派生数字.

I am assuming I need some type of subquery..but this is where I need your help... How would I word the query so that I can use calccolumn1 in another calculation within the same query? It might be an If then or a Case when, but bottomline it is some derived number.

推荐答案

您可以使用嵌套查询:

Select
  ColumnA,
  ColumnB,
  calccolumn1,
  calccolumn1 / ColumnC as calccolumn2
From (
  Select
    ColumnA,
    ColumnB,
    ColumnC,
    ColumnA + ColumnB As calccolumn1
  from t42
);

具有值 3、4、5 的一行给出:

With a row with values 3, 4, 5 that gives:

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4

你也可以只重复第一次计算，除非它真的在做一些昂贵的事情(比如通过函数调用):

You can also just repeat the first calculation, unless it's really doing something expensive (via a function call, say):

Select
  ColumnA,
  ColumnB,
  ColumnA + ColumnB As calccolumn1,
  (ColumnA + ColumnB) / ColumnC As calccolumn2
from t42; 

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4 

这篇关于如何使用计算列计算同一视图中的另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！