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'.
解决方法
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:
- 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.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
SELECT 2015AS _11
这将产生相同的结果
SELECT 2015_11
另外,为了验证我刚刚在上面写的内容,请参阅SSMS的屏幕截图,它在AS上执行代码突出显示
在您的第一个示例中,2015是整数字面值,在第二个示例中,2.1是十进制字面值
在第三个示例中,a不是有效的文字.如果你试试
SELECT 'a'_8
这会给你带来的结果
╔═════╗ ║ _8 ║ ╠═════╣ ║ a ║ ╚═════╝
PS：你会发现这与#的工作方式大致相同
所以SELECT 2015#11将给出类似的结果
╔══════╗ ║ #11 ║ ╠══════╣ ║ 2015 ║ ╚══════╝
