是否有通用的解决方法来表达Oracle(和MySQL)中的派生列列表?

问题描述

许多SQL数据库支持SQL标准称为<derived column list>的内容.此类数据库至少包括CUBRID，Derby，Firebird，HSQLDB，Postgres，SQL Server和Sybase SQL Anywhere. SQL:2008规范的(简化)摘录

Many SQL databases support what the SQL standard calls a <derived column list>. Such databases include at least CUBRID, Derby, Firebird, HSQLDB, Postgres, SQL Server, and Sybase SQL Anywhere. A (simplified) extract from the SQL:2008 specification

7.6 <table reference>

Format
<table reference> ::=
    <table or query name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
  | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]

这意味着，我可以表达这样的内容(例如，在Postgres中，它非常符合标准)

This means, I can express things like these (e.g. in Postgres, which is pretty standards-compliant)

-- Rename a <table or query name> to u(b)
with t(a) as (select 1)
select * from t as u(b)

-- Rename a <derived table> to u(b)
select * from (select 1) as u(b)

现在，根据 Oracle文档，我无法使用<derived column list>规范重命名列.我当然可以分别重命名表和列，就像这样:

Now, according to the Oracle documentation, I can't rename columns using a <derived column list> specification. I could of course rename tables and columns separately, like this:

-- Rename a <table or query name> to u(b)
with t(a) as (select 1 from dual)
select u.a b from t u;

-- Rename a <derived table> to u(b)
select u.a b from (select 1 a from dual) u;

但是，与以前的语法相比，这需要更多有关派生表(实际列名)的知识.另外，重命名的列仅在投影之后才可用(例如，在ORDER BY子句中)，而在包括投影本身在内的任何其他子句中均不可用.

But this requires more knowledge about the derived table (actual column names) than the previous syntax. Also, the renamed columns would only be available after the projection (e.g. in the ORDER BY clause), not in any other clauses, including the projection itself.

在Oracle(以及MySQL)中，是否存在一种更通用的方法来重命名表和列，就像SQL标准所建议的那样?特别是，这对于诸如数组取消嵌套，数据透视表/数据透视表重命名，内联复杂子查询，表功能的重命名等工作很有用.

Is there a more generic way to rename tables AND columns the way the SQL standard suggests, in Oracle (and also MySQL)? In particular, this could be useful for things like array unnesting, pivot/unpivot table renaming, inlining complex subqueries, renaming results from table functions, etc.

N.B:请不要过多地关注上述示例.他们确实只是在这里说明问题.实际查询要复杂得多，因此我正在寻找一种非常通用的方法来实现重命名为u(b)

N.B: Please do not focus on the above examples too much. They're really just here to illustrate the problem. Real-world queries are much more complex, so I'm looking for a very general way to implement renaming to u(b)

注意:我仍在寻找一种适用于像MySQL这样的数据库的解决方案.相关问题:
如何从子选择中选择未混淆的数字文字

NOTE: I'm still looking for a solution that works on a database like MySQL. A related question:
How to select an unaliased numeric literal from a sub-select

推荐答案

对于MySQL解决方案，您可以使用UNION设置零行查询项中所有列的名称，然后查询一些内容更复杂:

For a MySQL solution, you could use a UNION to set the names of all the columns in a zero-row query term, and then subsequently query something more complex:

SELECT null AS a, null AS b, null AS c FROM dual WHERE false
UNION ALL
SELECT <expr>, <expr>, <expr>
FROM <realtable>...

仅UNION的第一个查询项定义了整个查询的列名.后续查询字词中的列名(或缺少列名)不会影响最终的列名.

Only the first query term of a UNION defines the column names of the whole query. Column names (or lack thereof) in subsequent query terms don't affect the ultimate column names.

您确实需要知道列的 number ，但是将两个查询词分开很容易.据我所知，它可以在Oracle和MySQL中运行(但是，我仅在MySQL中进行过测试，而在Oracle中未进行过测试.)

You do need to know the number of columns, but it should be pretty easy to keep the two query terms separate. As far as I know, it works in both Oracle and MySQL (however, I have only tested it in MySQL, not in Oracle).

这篇关于是否有通用的解决方法来表达Oracle(和MySQL)中的派生列列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！