select - 在派生选择或 View 中使用列别名会破坏简单的选择查询

Question

我有一个真正的用例，它需要在“where”子句中引用列别名。我正在尝试使用此处概述的技术，我希望这些技术适用于 Sybase 和 MySQL，但似乎不适用于 H2 或 HSQLDB: http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html

如果您愿意尝试重现我的问题，请按以下步骤操作:

create table CUSTOMER (code varchar(255), description varchar(255), active bit, accountOpeningDate date, currentBalance numeric(20,6), currentBalanceDigits int)
insert into CUSTOMER (code, description, active, accountOpeningDate, currentBalance, currentBalanceDigits) values ('BMW', 'BMW Motors', 0, '2011-01-01', 345.66, 2)
insert into CUSTOMER (code, description, active, accountOpeningDate, currentBalance, currentBalanceDigits) values ('MERC', 'Mercedes Motors', 1, '2012-02-02', 14032, 0)

然后，此 SQL 查询失败:

select nest.* from (
  select CODE "id", DESCRIPTION "description",
  ACTIVE "active",
  accountOpeningDate "accountOpeningDate",
  currentBalance "currentBalance"
  from customer
) as nest
where nest.id = 'BMW'

如果去掉“where nest.id = 'BMW'”就没问题了。但是，尝试在 where 子句或 select 子句中使用任何别名(nest.id 而不是 next.*)，查询将失败。错误代码是未找到列“NEST.ID”； ... [42122-167] 42S22/42122

如果您尝试创建一个具有别名列名的 View ，然后尝试从该 View 中进行选择，则会发生同样的失败。例如:

create view customer_view as 
select CODE "id", DESCRIPTION "description", 
ACTIVE "active", 
accountOpeningDate "accountOpeningDate", 
currentBalance "currentBalance" 
from customer

然后:

select id from customer_view

Answer 1

问题是不带引号和带引号的标识符的混合使用。根据 SQL 规范，不带引号的标识符(例如 id)不区分大小写，数据库可能会将它们转换为大写或小写。引号标识符(例如 "id")区分大小写，数据库引擎不得转换标识符。

H2 将未加引号的标识符转换为大写(就像其他数据库引擎，如 Oracle)。在您的查询中，您同时使用了带引号和不带引号的标识符。简化测试用例(H2 和其他数据库失败):

select * from (select 1 "id") where id = 1

要解决这个问题，要么到处使用带引号的标识符，要么不带引号的标识符:

select * from (select 1 id) where id = 1

或

select * from (select 1 "id") where "id" = 1