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

我希望你可以帮助这个问题。我使用 Oracle SQL (SQL 开发人员为这个视图) ..。

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

  • 列 A (数字)
  • B 栏(数字)
  • 列 C (数字)

在我看来是的

Select
ColumnA,
ColumnB,
ColumnA + ColumnB As calccolumn1

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

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

我假设我需要一些类型的子查询. . 但这是我需要你的帮助..。 如何对查询使用单词，以便在同一个查询中的另一个计算中使用 calccolumn1？它可能是一个如果然后或情况下，但底线是一些派生的数字。

269411 次浏览
小开
最佳答案

You could use a nested query:

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

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
小开

You have to include the expression for your calculated column:

SELECT
ColumnA,
ColumnB,
ColumnA + ColumnB AS calccolumn1
(ColumnA + ColumnB) / ColumnC AS calccolumn2
小开

In Sql Server

You can do this using cross apply

Select
ColumnA,
ColumnB,
c.calccolumn1 As calccolumn1,
c.calccolumn1 / ColumnC As calccolumn2
from t42
cross apply (select (ColumnA + ColumnB) as calccolumn1) as c
小开

If you want to refer to calculated column on the "same query level" then you could use CROSS APPLY(Oracle 12c):

--Sample data:
CREATE TABLE tab(ColumnA NUMBER(10,2),ColumnB NUMBER(10,2),ColumnC NUMBER(10,2));


INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (2, 10, 2);
INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (3, 15, 6);
INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (7, 14, 3);
COMMIT;

Query:

SELECT
ColumnA,
ColumnB,
sub.calccolumn1,
sub.calccolumn1 / ColumnC AS calccolumn2
FROM tab t
CROSS APPLY (SELECT t.ColumnA + t.ColumnB AS calccolumn1 FROM dual) sub;

DBFiddle Demo

Please note that expression from CROSS APPLY/OUTER APPLY is available in other clauses too:

SELECT
ColumnA,
ColumnB,
sub.calccolumn1,
sub.calccolumn1 / ColumnC AS calccolumn2
FROM tab t
CROSS APPLY (SELECT t.ColumnA + t.ColumnB AS calccolumn1 FROM dual) sub
WHERE sub.calccolumn1 = 12;
-- GROUP BY ...
-- ORDER BY ...;

This approach allows to avoid wrapping entire query with outerquery or copy/paste same expression in multiple places(with complex one it could be hard to maintain).

Related article: The SQL Language’s Most Missing Feature

小开

In SQL Server

 You can do this using With CTE

WITH common_table_expression (Transact-SQL)

CREATE TABLE tab(ColumnA DECIMAL(10,2), ColumnB DECIMAL(10,2), ColumnC DECIMAL(10,2))


INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (2, 10, 2),(3, 15, 6),(7, 14, 3)


WITH tab_CTE (ColumnA, ColumnB, ColumnC,calccolumn1)
AS
(
Select
ColumnA,
ColumnB,
ColumnC,
ColumnA + ColumnB As calccolumn1
from tab
)


SELECT
ColumnA,
ColumnB,
calccolumn1,
calccolumn1 / ColumnC AS calccolumn2
FROM  tab_CTE

DBFiddle Demo


提交答案