如何从子选择中选择无别名的数字文字 [英] How to select an unaliased numeric literal from a sub-select

问题描述

我想知道是否有一个通用的 SQL 语法允许从子选择中选择无别名的数字文字:

I'm wondering if there is a general SQL syntax allowing for selecting unaliased numeric literals from sub-selects:

-- Seems to work in MySQL / Oracle
select table_alias."1"
from (
   select 1 from dual
) table_alias

我知道我可以为子选择中的字段设置别名:

I know I could alias the fields in the subselect:

-- Works everywhere
select table_alias.column_alias
from (
   select 1 column_alias from dual
) table_alias

但是如果我无法控制子选择怎么办?此外，一些 RDBMS 允许在为表设置别名时同时提供表和列别名:

But what if I don't have control over the subselect? Also, some RDBMS allow to provide both table AND column aliases when aliasing tables:

-- Seems to work in Postgres / SQL Server
select table_alias.column_alias
from (
  select 1 from dual
) table_alias (column_alias)

但某些 RDBMS(例如 MySQL)无法做到这一点.还有其他方法吗?

But some RDBMS (e.g. MySQL) can't do that. Is there another way?

• 注意:这与任何特定的 RDBMS 无关，而只是一般的 SQL
• 注意:我想省略星号，即没有 select *...

这里有一个相关的问题:

A related question is this one here:

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

推荐答案

根据 ANSI-92 标准，它是依赖于实现的.从第 7.9、9.c 节开始:

According to the ANSI-92 standard it is implementation dependent. From section 7.9, 9.c:

否则，第 i 列的 <列名> 是依赖于实现的，与<列名> 引用的表的除自身以外的任何列通过 SQL 语句中包含的任何 <table reference>.

Otherwise, the <column name> of the i-th column of the is implementation-dependent and different from the <column name> of any column, other than itself, of a table referenced by any <table reference> contained in the SQL-statement.

换句话说，这一切都将取决于您当时使用的 RDBMS.

In other words, it's all going to depend on the RDBMS that you're using at the time.

顺便说一句，您可以查看 ANSI-92 标准 如果您正在寻找有趣的阅读.

BTW, you can check out the ANSI-92 standards if you're looking for some fun reading.

这篇关于如何从子选择中选择无别名的数字文字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！