sql server 2008-ORDER BY子句中的非整数常量 [英] sql server 2008 - non-integer constant in ORDER BY Clause

问题描述

升级顾问说

ORDER BY 子句在90或更高版本的
兼容模式下。

"Non-integer constants are not allowed in the ORDER BY clause in 90 or later compatibility mode".

但是，当我尝试在 SQL Server 2008 ，效果很好。因此，我的问题是-非整数常量到底是什么？

But, when I try the below statement in SQL Server 2008, it works just fine. So, my questions is - What exactly is a non-integer constant?

select 
    POS_NO
    ,EMP_NO
    ,ORG_NAME
    ,EMP_LAST_NAME + ', ' + EMP_FIRST_NAME AS "Name" 
FROM 
    dbo.MyEmpTable 
ORDER BY
    "Name"

推荐答案

这是 MSDN code>在某种意义上不得不说字符常量，即非整数常量

Here is what the MSDN have to say about character constant that are in a sense the non-integer constant

字符串常量用单引号引起来，
包括字母数字字符（az，AZ和0-9）和特殊的
字符，例如感叹号（！），位于符号（@）和数字
符号（＃）。除非为COLLATE子句使用
来指定排序规则，否则将为字符串常量分配当前数据库的默认
排序规则。用户键入的字符串将通过计算机的代码页进行评估，价格为
，并在需要时转换为
数据库的默认代码页。

Character string constants are enclosed in single quotation marks and include alphanumeric characters (a-z, A-Z, and 0-9) and special characters, such as exclamation point (!), at sign (@), and number sign (#). Character string constants are assigned the default collation of the current database, unless the COLLATE clause is used to specify a collation. Character strings typed by users are evaluated through the code page of the computer and are translated to the database default code page if it is required.

1）非整数常量是...不是整数的常量。

1) Non-integer constants are ... constants that are not integer number.

示例：
'string1'表示字符串常量

0x01 表示一个可变常数

{ts'2015- 02-26 06:00:00'} 代表日期时间常量

1.23 表示数字常量

2）因此，单引号用于定义字符串常量/字符串常量但 SQL Server允许使用单引号引号也用作列标识符定界符：

2) So single quotes are used to define a string constants / character string constants but SQL Server allows also to use single quotation marks use also as column identifier delimiter:

SELECT ... expression AS 'Column1'
FROM ...

在这种情况下，很明显'Column1'是列标识符，但在ORDER BY中使用时： ORDER BY'Column1'它会引起混淆，因为SQL Server不知道它是否表示字符串文字（字符串常量）或代表列标识符/列名。

In this context is clear that 'Column1' is a column identifier but when used in ORDER BY : ORDER BY 'Column1' it generates confusion because SQL Server doesn't knows if it represents a string literal (character string constant) or it represents a column identifier / column name.

3）SQL Server al低点在ORDER BY中使用整数常量，因此 SELECT ColA，ColB，ColC FROM ... ORDER BY 2 。在这种情况下， 2 是列 ColB 的索引。另外，如果要按 ColB 和 ColC 排序，可以使用 ORDER BY 2， 3 。

3) SQL Server allows to use use integer constants in ORDER BY thus SELECT ColA, ColB, ColC FROM ... ORDER BY 2. In this case 2 is the index's of column ColB. Also, if you want to sort by ColB and ColC you could use ORDER BY 2, 3. Usage of column's index is considered to be bad practice.

4）在这种情况下，我将使用

4) In this case I would use

ORDER BY EMP_LAST_NAME + ', ' + EMP_FIRST_NAME

这篇关于sql server 2008-ORDER BY子句中的非整数常量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！