使用 SQL 序号位置表示法的好处?

2021-12-06 00:00:00 sql oracle sql-server ordinals

序数位置表示法,又名序数,是基于 SELECT 子句中列列表中的列顺序的列简写,而不是列名称或列别名.ORDER BY 子句普遍支持,一些数据库(MySQL 3.23+,PostgreSQL 8.0+)也支持 GROUP BY 子句的语法.

Ordinal position notation, AKA ordinals, is column shorthand based on the column order in the list of columns in the SELECT clause, instead of either the column name or column alias. Commonly supported in the ORDER BY clause, some databases (MySQL 3.23+, PostgreSQL 8.0+) support the syntax for the GROUP BY clause as well.

以下是使用序数的示例:

Here's an example of using Ordinals:

GROUP BY 1, 2
ORDER BY 1, 2

它不好使用,因为它使查询变得脆弱 - 如果列顺序发生变化,则需要更新序数,否则您的查询将不会返回您认为的内容.如果这些位置的列包含在聚合中,则很可能在 GROUP BY 中使用时会出现错误...

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 BY if the columns at those locations are wrapped within aggregates...

我能想到的唯一好处是,如果您不使用存储过程或函数(这对我来说,顺序使用毫无意义),可以通过网络发送更少的数据.我还缺少其他任何好处吗?

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?

这听起来像是一项家庭作业,但它实际上是为了办公室每月举办的教育午餐而进行的研究.他们支付午餐费用,我们必须提供一个感兴趣的小话题.

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.

推荐答案

我会使用它:

  • 如果您喜欢故障排除
  • 在没有智能感知的情况下创建临时查询

没有好处.

SQL Server 无论如何只支持 ORDER BY.在其他任何地方,它都是要评估的表达式.

SQL Server only supports in the ORDER BY anyway. Anywhere else it's an expression to be evaluated.

相关文章