ORDER BY只适用于列吗？

Why does SELECT * FROM TABLE ORDER BY RAND() Work? I thought ORDER BY only works for columns.

为什么SELECT * FROM TABLE ORDER BY RAND()有效?我认为ORDER BY只适用于列。

So what exactly does it mean to ORDER BY RAND() or ORDER BY SUM()?

那么ORDER BY RAND()或ORDER BY SUM()究竟是什么意思呢?

4 个解决方案

#1

ORDER will work with any value you can put in your results (but doesn't have to be one of the values in the results). This can be a column in any of the source tables or calculated using a function. For example, you could use ORDER UPPER(name) for a case-insensitive sort.

ORDER将使用您可以在结果中添加的任何值(但不必是结果中的值之一)。这可以是任何源表中的列,也可以使用函数计算。例如,您可以使用ORDER UPPER(name)进行不区分大小写的排序。

If you ORDER BY RAND() you're ordering by a random number generated for each row in the results, i.e. returning the rows in a random order. If you're ordering by SUM() you've probably got a GROUP BY in there too so you could order customers by total calculated invoice total for example.

如果ORDER BY RAND()您按照为结果中的每一行生成的随机数进行排序,即以随机顺序返回行。如果您通过SUM()订购,那么您可能也有一个GROUP BY,因此您可以按总计算发票总额订购客户。

Ideally you want to use a column from an index as this will be much faster.

理想情况下,您希望使用索引中的列,因为这将更快。

#2

You can order by nearly everything, functions (like RAND()), aggregations (like SUM()) and so on.

你可以通过几乎所有的东西,函数(如RAND()),聚合(如SUM())等来订购。

For example, the MySQL-Documentation states as Syntax:

例如,MySQL-Documentation表示为语法:

 [ORDER BY {col_name | expr | position}

Or the Postgresql Documentation is even more explicit:

或者Postgresql文档更加明确:

The optional ORDER BY clause has this general form:

可选的ORDER BY子句具有以下通用形式:

ORDER BY expression [ ASC | DESC | USING operator ] [, ...]

ORDER BY表达式[ASC | DESC |使用运算符] [,...]

expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

expression可以是输出列的名称或序号(SELECT列表项),也可以是由输入列值组成的任意表达式。

For your second question:

对于你的第二个问题:

ORDER BY RAND() or ORDER BY RANDOM() does what it states: Your rows are shuffeled and you get them in a random order. So if you do a SELECT * FROM ... ORDER BY RAND() LIMIT 1 you select a (=one) random row out of your table.

ORDER BY RAND()或ORDER BY RANDOM()执行它所声明的内容:您的行被拆分并且您以随机顺序获取它们。因此,如果您执行SELECT * FROM ... ORDER BY RAND()LIMIT 1,则从表中选择一个(= 1)随机行。

ORDER BY SUM(Column) only makes sense in combination with a GROUP BY statement.

ORDER BY SUM(Column)仅与GROUP BY语句结合才有意义。

#3

Actually it depends on the implementation.The statement you mentioned is not SQL92-compatible but it may be accepted by an implementation.

实际上它取决于实现。您提到的语句不是SQL92兼容的,但它可能被实现所接受。

#4

Well, to put it short: You thought wrong.

好吧,简而言之:你错了。

#1