使用下划线解析SQL Server编号文字

SELECT 2015_11

结果：

╔══════╗
║ _11  ║
╠══════╣
║ 2015 ║
╚══════╝

第二种情况：

SELECT 2.1_a

╔═════╗
║ _a  ║
╠═════╣
║ 2.1 ║
╚═════╝

检查元数据：

SELECT  name,system_type_name
FROM sys.dm_exec_describe_first_result_set(
N'SELECT 2015_11',NULL,0) 
UNION ALL
SELECT  name,system_type_name
FROM sys.dm_exec_describe_first_result_set(
N'SELECT 3.2_a',0) 

╔══════╦══════════════════╗
║ name ║ system_type_name ║
╠══════╬══════════════════╣
║ _11  ║ int              ║
║ _a   ║ numeric(2,1)     ║
╚══════╩══════════════════╝

虽然以字母开头的标识符表现得像我认为的那样：

SELECT a_11
-- Invalid column name 'a_11'.

LiveDemo

解决方法

SELECT 2015_11

如

SELECT 2015 _11

这是捷径

SELECT 2015 AS [_11]

SQL Server期望列名遵循一些命名约定规则,如本MSDN link中所述

The names of variables,functions,and stored procedures must comply with the following rules for Transact-SQL identifiers.
The first character must be one of the following:

1. A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z,from
   A through Z,and also letter characters from other languages.

2. The underscore (_),at sign (@),or number sign (#).

   Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at
   sign always denotes a local variable or parameter and cannot be used
   as the name of any other type of object. An identifier that starts
   with a number sign denotes a temporary table or procedure. An
   identifier that starts with double number signs (##) denotes a global
   temporary object. Although the number sign or double number sign
   characters can be used to begin the names of other types of objects,
   we do not recommend this practice.

Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions,you should not
use names that start with @@.

根据MSDN的SELECT语法也是如此

SELECT [ ALL | DISTINCT ] [ TOP ( expression ) [ PERCENT ] [ WITH TIES
] ] ::=
{
*
| { table_name | view_name | table_alias }.*
| {
[ { table_name | view_name | table_alias }. ]
{ column_name | $IDENTITY | $ROWGUID }
| udt_column_name [ { . | :: } { { property_name | field_name }
| method_name ( argument [,…n] ) } ]
| expression
[ [ AS ] column_alias ]
}
| column_alias = expression
} [,…n ]

在这种情况下,SQL解析器首先检查表名,然后检查列名,Identity和rowguid,依此类推,直到它与

|表达式[[AS] column_alias]

然后它读取字面值直到下划线字符,这是当它意识到文字必须已经结束并开始将后面的字符解析为Column_alias而没有显式AS

要验证这一点,请尝试在SQL Server中执行以下代码

SELECT 2015AS _11

这将产生相同的结果

SELECT 2015_11

另外,为了验证我刚刚在上面写的内容,请参阅SSMS的屏幕截图,它在AS上执行代码突出显示

在您的第一个示例中,2015是整数字面值,在第二个示例中,2.1是十进制字面值

在第三个示例中,a不是有效的文字.如果你试试

SELECT 'a'_8

这会给你带来的结果

╔═════╗
║ _8  ║
╠═════╣
║ a   ║
╚═════╝

PS：你会发现这与#的工作方式大致相同

所以SELECT 2015#11将给出类似的结果

╔══════╗
║ #11  ║
╠══════╣
║ 2015 ║
╚══════╝

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!