使用 SQL 序数位置表示法的好处？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2253040/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Benefits Of Using SQL Ordinal Position Notation?
提问by OMG Ponies
Background Information
背景资料
Ordinal position notation, AKA ordinals, is column shorthand based on the column order in the list of columns in the SELECTclause, instead of either the column name or column alias. Commonly supported in the ORDER BYclause, some databases (MySQL 3.23+, PostgreSQL 8.0+) support the syntax for the GROUP BYclause as well.
序数位置表示法（AKA ordinals）是基于SELECT子句中列列表中的列顺序的列简写，而不是列名称或列别名。在ORDER BY子句中普遍支持，一些数据库（MySQL 3.23+，PostgreSQL 8.0+）也支持GROUP BY子句的语法。
Here's an example of using Ordinals:
下面是一个使用序数的例子：
GROUP BY 1, 2
ORDER BY 1, 2
It's not good to use because it makes the query brittle - if the column order changes, the ordinals need to be updated or your query won't return what you thought it would. Very likely, you'd get an error when used in the GROUP BYif the columns at those locations are wrapped within aggregates...
它不好使用，因为它使查询变得脆弱 - 如果列顺序发生变化，则需要更新序数，否则您的查询将不会返回您认为的内容。很可能，GROUP BY如果在这些位置的列包含在聚合中，则在使用时会出现错误...
The Question
问题
The only benefit I can think of is less data to send over the wire, if you aren't using stored procedures or functions (which make ordinal usage moot, to me anyways). Are there any other benefits I'm missing?
我能想到的唯一好处是，如果您不使用存储过程或函数（无论如何对我来说，顺序使用没有实际意义），通过线路发送的数据更少。我还缺少其他任何好处吗？
Disclosure
披露
This might sound like a homework assignment, but it's really research for an educational lunch the office puts on every month. They pay for lunch, we have to provide a small topic of interest.
这听起来像是一项家庭作业，但它实际上是为办公室每个月提供的教育午餐而进行的研究。他们付午餐费，我们必须提供一个小话题。
回答by gbn
I'd use it:
我会用它：
- If you love troubleshooting
- Creating adhoc queries without intellisense
- 如果您喜欢故障排除
- 在没有智能感知的情况下创建临时查询
There is no upside.
没有任何好处。
SQL Server only supports in the ORDER BY anyway. Anywhere else it's an expression to be evaluated.
无论如何，SQL Server 只支持 ORDER BY。在其他任何地方，它都是要评估的表达式。
回答by mattmc3
Often times when I'm querying a table with a lot of columns (in ad-hoc-land just for data exploration... I would never code like this for a PROD environment) I do something like this to get fields I care about close together:
很多时候，当我查询一个包含很多列的表时（在 ad-hoc-land 中只是为了数据探索......我永远不会为 PROD 环境编写这样的代码）我会做这样的事情来获取我关心的字段紧靠在一起：
select top 1000
Col_1, Col_18, Col_50, Col_117, *
from
TableWithTonsOfCols
order by
1, 4 desc, 3
If I said order by Col_1, Col_117 desc, Col_50my query would barf because the statement wouldn't know which columns I meant to order by due to the " * " doubling up. Not very common, but still a useful feature.
如果我说order by Col_1, Col_117 desc, Col_50我的查询会失败，因为由于“ * ”加倍，语句将不知道我打算按哪些列排序。不是很常见，但仍然是一个有用的功能。
回答by RedFilter
The two use cases for me are:
我的两个用例是：
- I am in a hurry and don't want to type, so I use the ordinal. I would always convert this to the column name for any non-temporary use
- the column I am ordering by is a lengthy
CASEstatement; rather than retyping theCASEstatement for theORDER BYclause, I use the ordinal which keeps it DRY. There are ways around this, e.g., using CTEs, subqueries, or view, but I often find the ordinal is the simplest solution.
- 我赶时间不想打字，所以我用序数。我总是将其转换为任何非临时使用的列名
- 我订购的那一栏是一个冗长的
CASE陈述；我没有重新输入子句的CASE语句ORDER BY，而是使用保持它DRY的序数。有很多方法可以解决这个问题，例如，使用 CTE、子查询或视图，但我经常发现序数是最简单的解决方案。
回答by Gary Myers
I tend to use in-line views now:
我现在倾向于使用内嵌视图：
select col_a, count(*) from
(select case ...... end col_a from ...)
group by col_a
order by col_a;
But in the days before they were valid syntax, it did help retyping the full text of the column. With tricky functions you had the potential for discrepancies between the value in the SELECT and ORDER BY such as
但在它们成为有效语法之前的日子里，它确实有助于重新键入该列的全文。使用棘手的函数，您可能会在 SELECT 和 ORDER BY 中的值之间出现差异，例如
select ltrim(col_name,'0123456789')
from table
order by ltrim(col_name,'123456789')
The '0' in the SELECT means that you are not ordering by what you select.
SELECT 中的“0”表示您没有按您选择的内容排序。
回答by Perry Sugerman
I have a query generating algorithm - the SQL is auto generated. Using the ordinal means that I can refer to the generated field without having to fetch the field name again. The user can refer to the field name in a table by selecting it from a list on the screen. As long as I make the list correspond with the sql, I would never need to know field names, if the SELECT items were ordinal, too.
我有一个查询生成算法 - SQL 是自动生成的。使用序数意味着我可以引用生成的字段而无需再次获取字段名称。用户可以通过从屏幕上的列表中选择来引用表中的字段名称。只要我使列表与 sql 对应，我就永远不需要知道字段名称，如果 SELECT 项也是有序的。
Memory says this used to be in the SQL standard in the late 1970's
内存说这曾经是 1970 年代后期的 SQL 标准
回答by Randy Minder
If I recall correctly, the use of ordinals like you describe is being deprecated by Microsoft in a future release of SQL Server. I could be wrong on this, but I think that's the case. I've always liked using them in certain cases because it involves less typing when you're dealing with derived columns that contain a longish query.
如果我没记错的话，Microsoft 在 SQL Server 的未来版本中将不推荐使用您所描述的序数。我可能在这一点上是错的，但我认为情况就是这样。我一直喜欢在某些情况下使用它们，因为在处理包含较长查询的派生列时，它涉及较少的输入。
