如何在 Oracle SQL 语句中重用动态列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/743772/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reuse dynamic columns in an Oracle SQL statement?
提问by BlackShift
I try to reuse some columns that I calculate dynamically in Oracle SQL, something like
我尝试重用我在 Oracle SQL 中动态计算的一些列，例如
SELECT
A*2 AS P,
P+5 AS Q
FROM tablename
Where 'tablename' has a column called 'A', but no other colums. This gives me an
其中 'tablename' 有一个名为 'A' 的列，但没有其他列。这给了我一个
ORA-00904: "P": invalid identifier
I know how to work around this by using a subquery like
我知道如何通过使用子查询来解决这个问题
SELECT P, P+5 AS Q
FROM ( SELECT A*2 AS P FROM tablename )
but I think this is kinda ugly. Furthermore I want to make the query somewhat more complex, e.g. reusing 'Q' as well, and I do not want to create yet another subquery.
但我觉得这有点丑。此外，我想让查询更复杂一些，例如重用“Q”，并且我不想创建另一个子查询。
Update: The reason I want to store the calculation of 'P' is that I want to make it more complex, and reuse 'P' multiple times. So I do not want to explicitly say 'A*2+5 AS Q', because that would quickly become to cumbersome as 'P' gets more complex.
更新：我想存储 'P' 的计算的原因是我想让它更复杂，并多次重用 'P'。所以我不想明确地说“A*2+5 AS Q”，因为随着“P”变得更加复杂，这很快就会变得很麻烦。
There must be a good way to do this, any ideas?
必须有一个很好的方法来做到这一点，有什么想法吗？
Update: I should note that I'm not a DB-admin :(.
更新：我应该注意，我不是数据库管理员 :(。
更新：一个真实世界的例子，有一个更具体的查询。我想做的是：
SELECT
SL/SQRT(AB) AS ALPHA,
5*LOG(10,ALPHA) AS B,
2.5*LOG(10,1-EXP(-5/ALPHA)*(5/ALPHA+1)) AS D
BS -2.74 + B + D AS BSA
FROM tablename
for now, I've written it out, which works, but is ugly:
现在，我已经把它写出来了，它有效，但很难看：
SELECT
SL/SQRT(AB) AS ALPHA,
5*LOG(10,SL/SQRT(AB)) AS B,
2.5*LOG(10,1-EXP(-5/(SL/SQRT(AB)))*(5/(SL/SQRT(AB))+1)) AS D
BS -2.74 + 5*LOG(10,SL/SQRT(AB)) + 2.5*LOG(10,1-EXP(-5/(SL/SQRT(AB)))*((5/(SL/SQRT(AB)))+1)) AS BSA
FROM tablename
I could do all of this after receiving the data, but I thought, let's see how much I can let the database do. Also, I would like to select on 'BSA' as well (which I can do now with this query as a subquery/with-clause).
收到数据后我可以做所有这些，但我想，让我们看看我可以让数据库做多少。另外，我也想选择“BSA”（我现在可以将此查询作为子查询/with-clause）。
Update: OK, I think for now I finished with Cade Roux' and Dave Costa's solution. Albeit Pax' and Jens Schauder's solution would look better, but I can't use them since I'm not a DBA. Now I don't know who to mark as the best answer :).
更新：好的，我想现在我已经完成了 Cade Roux 和 Dave Costa 的解决方案。尽管 Pax 和 Jens Schauder 的解决方案看起来更好，但我不能使用它们，因为我不是 DBA。现在我不知道该将谁标记为最佳答案:)。
WITH
A1 AS (
SELECT A0.*,
SL/SQRT(AB) AS ALPHA
FROM tablename A0
),
A2 AS (
SELECT A1.*,
5*LOG(10,ALPHA) AS B,
2.5*LOG(10,1-EXP(-5/ALPHA)*((5/ALPHA)+1)) AS D
FROM A1
)
SELECT
ALPHA, B, D, BS,
BS -2.74 + B + D AS BSA
FROM A2
BTW, in case anyone is interested, SB is the 'surface brightness' of galaxies, for which B and D are correction terms.
顺便说一句，如果有人感兴趣，SB 是星系的“表面亮度”，其中 B 和 D 是校正项。
采纳答案by Cade Roux
We have this same problem in SQL Server (it's an ANSI problem). I believe it is intended to avoid confusing aliasing effects:
我们在 SQL Server 中有同样的问题（这是一个 ANSI 问题）。我相信这是为了避免混淆混叠效果：
SELECT A * 2 AS A
,A * 3 AS B -- This is the original A, not the new A
FROM whatever
We work around it by stacking up common table expressions:
我们通过堆叠公共表表达式来解决它：
WITH A1 AS (
SELECT A * 2 AS A
FROM whatever
)
,A2 AS (
SELECT A1.*
,A * 3 AS B
FROM A1
)
,A3 AS (
SELECT A2.*
,A + B AS X
FROM A2
)
SELECT *
FROM A3
This is the most readable and maintable and followable version.
这是最具可读性、可维护性和可遵循性的版本。
For UPDATEs, there is a deprecated SQL Server workaround using the column_name = notation, where you can reference a column which has been updated previously in the list. But this cannot be used in SELECTs.
对于更新，有一个不推荐使用的 SQL Server 解决方法使用 column_name = 表示法，您可以在其中引用列表中先前已更新的列。但这不能在 SELECT 中使用。
I would hope that some ability to stack expressions (without using a scalar UDF) is added to ANSI SQL at some point in the future.
我希望在将来的某个时候将一些堆叠表达式的能力（不使用标量 UDF）添加到 ANSI SQL。
回答by Galal Ouda
Alias reusing is common in Teradata however it can be confusion sometimes mainly when you alias a column name with a name that exists within the table/subquery and try to reuse it, the database will use the original column and not the one you aliased.
别名重用在 Teradata 中很常见，但是有时可能会造成混淆，主要是当您使用表/子查询中存在的名称为列名设置别名并尝试重用它时，数据库将使用原始列而不是您别名的列。
回答by paxdiablo
I'm not sure you can do this (I've never seen it done) but you could work around it with:
我不确定你能做到这一点（我从来没有见过它做过），但你可以解决这个问题：
SELECT
A*2 AS P,
A*2+5 AS Q
FROM tablename
That's certainly better than introducing a subquery.
这肯定比引入子查询要好。
The only other way I'd suggest is to create a view giving you the P/Q-type columns (using the formulae above) which would at least simplify the text of the query. Then you could just:
我建议的唯一另一种方法是创建一个视图，为您提供 P/Q 类型的列（使用上面的公式），这至少可以简化查询的文本。那么你可以：
SELECT P, Q FROM viewintotablename
回答by Quassnoi
You can't.
你不能。
If you don't want the subquery to be reevaluated, add a NO_MERGEhint for the subquery:
如果您不想重新评估子查询，请NO_MERGE为子查询添加提示：
This subquery will be reevaluated in the nested loop (MERGEhint is used):
此子查询将在嵌套循环中重新计算（使用MERGE提示）：
SELECT /*+ LEADING(g) USE_NL(g, r) MERGE(g) */
*
FROM (
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
) r,
(
SELECT SYS_GUID() AS guid
FROM dual d
) g
---
33CA48C1AB6B4403808FB0219302CE43
711BB04F9AFC406ABAEF8A8F4CFA1266
This subquery will not be reevaluated in the nested loop (NO_MERGEhint is used):
此子查询不会在嵌套循环中重新计算（使用NO_MERGE提示）：
SELECT /*+ LEADING(g) USE_NL(g, r) NO_MERGE(g) */
*
FROM (
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
) r,
(
SELECT SYS_GUID() AS guid
FROM dual d
) g
------
7715C69698A243C0B379E68ABB55C088
7715C69698A243C0B379E68ABB55C088
In your case, just write:
在你的情况下，只需写：
SELECT BS - 2.74 + d
FROM (
SELECT t2.*, 2.5 * LOG(10, 1 - EXP(-5 / b)) * ((5 / A) + 1) AS d
FROM (
SELECT t1.*, 5 * LOG(10, alpha) AS b
FROM (
SELECT /*+ NO_MERGE */ t.*,
SL/SQRT(AB) AS alpha
FROM tablename t
) t1
) t2
) t3
, which is more efficient (EXPand LOGare costly) and is much easier to debug.
，这更有效（EXP并且LOG成本更高）并且更容易调试。
回答by Jens Schauder
There is no direct way to do this in sql.
在 sql 中没有直接的方法可以做到这一点。
But you could define a function using PL/SQL. So your select would look like this
但是您可以使用 PL/SQL 定义一个函数。所以你的选择看起来像这样
select
P(A),
Q(P(A))
from tablename
For P and Q this isn't (much) better then the original, but if the functions are complex, and don't have to many parameter, it might make your statement much more readable.
对于 P 和 Q，这并不比原始的好（很多），但是如果函数很复杂，并且不需要很多参数，它可能会使您的语句更具可读性。
It also would allow you to test you functions independently from the sql statement, and any data.
它还允许您独立于 sql 语句和任何数据来测试您的功能。
回答by Dave Costa
You might like this a little better than the inline view example you gave:
您可能比您提供的内联视图示例更喜欢这个：
WITH inner_view AS ( SELECT A*2 AS P FROM tablename )
SELECT P, P+5 AS Q
FROM inner_view
It amounts to the same thing but it's a little clearer to read, I think.
它相当于同一件事，但我认为阅读起来更清晰一些。
If the computed column is something you will use in multiple columns, it may make sense to create a permanent view.
如果计算列是您将在多列中使用的内容，则创建永久视图可能是有意义的。
Oracle 11 (which I haven't used yet) has a virtual column feature that might be useful for you.
Oracle 11（我还没有使用过）有一个虚拟列特性，可能对你有用。
