如何从子选择中选择未混淆的数字文字 [英] 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 *
...
- Note: This isn't about any specific RDBMS, but just SQL in general
- Note: I'd like to omit the asterisk, i.e. no
select *
...
这里是一个相关的问题:
A related question is this one here:
Is there a generic workaround to express a derived column list in Oracle (and 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语句中包含的任何
引用的
表的
本身以外的任何列。 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屋!
查看全文登录 关闭
扫码关注1秒登录发送“验证码”获取 | 15天全站免登陆