SQL select语句中的Order By 1的目的是什么?

I'm reading through some old code at work, and have noticed that there are several views with an order by 1 clause. What does this accomplish?

我正在阅读一些旧代码，并注意到有几个视图具有order by 1子句。这做什么呢?

Example:

例子:

Create view v_payment_summary AS
SELECT A.PAYMENT_DATE,
       (SELECT SUM(paymentamount)
          FROM payment B
         WHERE PAYMENT_DATE = B.PAYMENT_DATE
           and SOME CONDITION) AS SUM_X,
       (SELECT SUM(paymentamount)
          FROM payment B
         WHERE PAYMENT_DATE = B.PAYMENT_DATE
           and SOME OTHER CONDITION) AS SUM_Y    
FROM payment A    
ORDER BY 1;

8 个解决方案

#1

This:

这样的:

ORDER BY 1

...is known as an "Ordinal" - the number stands for the column based on the number of columns defined in the SELECT clause. In the query you provided, it means:

…被称为“序号”——根据SELECT子句中定义的列数，数字表示列。在您提供的查询中，它表示:

ORDER BY A.PAYMENT_DATE

It's not a recommended practice, because:

这不是一种推荐的做法，因为:

1. It's not obvious/explicit
2. 这不是明显的/显式
3. If the column order changes, the query is still valid so you risk ordering by something you didn't intend
4. 如果列顺序发生变化，查询仍然有效，因此您可能会使用您不打算使用的内容进行排序

#2

This is useful when you use set based operators e.g. union

当您使用基于集合的运算符(如union)时，这是很有用的

select cola
  from tablea
union
select colb
  from tableb
order by 1;

#3

I believe in Oracle it means order by column #1

我相信Oracle，它意味着列1的顺序

#4

This will sort your results by the first column returned. In the example it will sort by payment_date.

这将对返回的第一列进行排序。在这个示例中，它将按payment_date排序。

#5

it simply means sorting the view or table by 1st column of query's result.

它仅仅意味着根据查询结果的第一列对视图或表进行排序。

#6

As mentioned in other answers ORDER BY 1 orders by the first column.

如其他答案所述，由第一列的1个顺序排列。

I came across another example of where you might use it though. We have certain queries which need to be ordered select the same column. You would get a SQL error if ordering by Name in the below.

我还遇到了另一个你可以使用它的例子。我们有一些查询需要排序，选择相同的列。如果按下面的名称排序，您将会得到一个SQL错误。

SELECT Name, Name FROM Segment ORDER BY 1

#7

Also see:

还看到:

http://www.techonthenet.com/sql/order_by.php

http://www.techonthenet.com/sql/order_by.php

For a description of order by. I learned something! :)

关于订单的描述。我学会了一些!:)

I've also used this in the past when I wanted to add an indeterminate number of filters to a sql statement. Sloppy I know, but it worked. :P

在过去，当我想在sql语句中添加不确定数量的过滤器时，我也使用过这个方法。我知道这很草率，但它确实奏效了。:P

#8

An example here from a sample test WAMP server database:-

这里有一个示例测试WAMP服务器数据库:-

mysql> select * from user_privileges;

| GRANTEE            | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
   +--------------------+---------------+-------------------------+--------------+
| 'root'@'localhost' | def           | SELECT                  | YES          |
| 'root'@'localhost' | def           | INSERT                  | YES          |
| 'root'@'localhost' | def           | UPDATE                  | YES          |
| 'root'@'localhost' | def           | DELETE                  | YES          |
| 'root'@'localhost' | def           | CREATE                  | YES          |
| 'root'@'localhost' | def           | DROP                    | YES          |
| 'root'@'localhost' | def           | RELOAD                  | YES          |
| 'root'@'localhost' | def           | SHUTDOWN                | YES          |
| 'root'@'localhost' | def           | PROCESS                 | YES          |
| 'root'@'localhost' | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | REFERENCES              | YES          |
| 'root'@'localhost' | def           | INDEX                   | YES          |
| 'root'@'localhost' | def           | ALTER                   | YES          |
| 'root'@'localhost' | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost' | def           | SUPER                   | YES          |
| 'root'@'localhost' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | LOCK TABLES             | YES          |
| 'root'@'localhost' | def           | EXECUTE                 | YES          |
| 'root'@'localhost' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost' | def           | CREATE VIEW             | YES          |
| 'root'@'localhost' | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost' | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost' | def           | CREATE USER             | YES          |
| 'root'@'localhost' | def           | EVENT                   | YES          |
| 'root'@'localhost' | def           | TRIGGER                 | YES          |
| 'root'@'localhost' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'127.0.0.1' | def           | SELECT                  | YES          |
| 'root'@'127.0.0.1' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | UPDATE                  | YES          |
| 'root'@'127.0.0.1' | def           | DELETE                  | YES          |
| 'root'@'127.0.0.1' | def           | CREATE                  | YES          |
| 'root'@'127.0.0.1' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | RELOAD                  | YES          |
| 'root'@'127.0.0.1' | def           | SHUTDOWN                | YES          |
| 'root'@'127.0.0.1' | def           | PROCESS                 | YES          |
| 'root'@'127.0.0.1' | def           | FILE                    | YES          |
| 'root'@'127.0.0.1' | def           | REFERENCES              | YES          |
| 'root'@'127.0.0.1' | def           | INDEX                   | YES          |
| 'root'@'127.0.0.1' | def           | ALTER                   | YES          |
| 'root'@'127.0.0.1' | def           | SHOW DATABASES          | YES          |
| 'root'@'127.0.0.1' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'127.0.0.1' | def           | LOCK TABLES             | YES          |
| 'root'@'127.0.0.1' | def           | EXECUTE                 | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'127.0.0.1' | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | SHOW VIEW               | YES          |
| 'root'@'127.0.0.1' | def           | CREATE ROUTINE          | YES          |
| 'root'@'127.0.0.1' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | CREATE USER             | YES          |
| 'root'@'127.0.0.1' | def           | EVENT                   | YES          |
| 'root'@'127.0.0.1' | def           | TRIGGER                 | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'::1'       | def           | SELECT                  | YES          |
| 'root'@'::1'       | def           | INSERT                  | YES          |
| 'root'@'::1'       | def           | UPDATE                  | YES          |
| 'root'@'::1'       | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | DROP                    | YES          |
| 'root'@'::1'       | def           | RELOAD                  | YES          |
| 'root'@'::1'       | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | FILE                    | YES          |
| 'root'@'::1'       | def           | REFERENCES              | YES          |
| 'root'@'::1'       | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | SHOW DATABASES          | YES          |
| 'root'@'::1'       | def           | SUPER                   | YES          |
| 'root'@'::1'       | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'::1'       | def           | LOCK TABLES             | YES          |
| 'root'@'::1'       | def           | EXECUTE                 | YES          |
| 'root'@'::1'       | def           | REPLICATION SLAVE       | YES          |
| 'root'@'::1'       | def           | REPLICATION CLIENT      | YES          |
| 'root'@'::1'       | def           | CREATE VIEW             | YES          |
| 'root'@'::1'       | def           | SHOW VIEW               | YES          |
| 'root'@'::1'       | def           | CREATE ROUTINE          | YES          |
| 'root'@'::1'       | def           | ALTER ROUTINE           | YES          |
| 'root'@'::1'       | def           | CREATE USER             | YES          |
| 'root'@'::1'       | def           | EVENT                   | YES          |
| 'root'@'::1'       | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | CREATE TABLESPACE       | YES          |
| ''@'localhost'     | def           | USAGE                   | NO           |
+--------------------+---------------+-------------------------+--------------+
85 rows in set (0.00 sec)

And when it is given additional order by PRIVILEGE_TYPE or can be given order by 3 . Notice the 3rd column (PRIVILEGE_TYPE) getting sorted alphabetically.

当它被PRIVILEGE_TYPE赋予了额外的次序或者可以被3赋予次序。注意第3列(PRIVILEGE_TYPE)按字母顺序排列。

mysql> select * from user_privileges order by PRIVILEGE_TYPE;
+--------------------+---------------+-------------------------+--------------+
| GRANTEE            | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+--------------------+---------------+-------------------------+--------------+
| 'root'@'127.0.0.1' | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | ALTER                   | YES          |
| 'root'@'localhost' | def           | ALTER                   | YES          |
| 'root'@'::1'       | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1' | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | CREATE                  | YES          |
| 'root'@'localhost' | def           | CREATE                  | YES          |
| 'root'@'::1'       | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost' | def           | CREATE ROUTINE          | YES          |
| 'root'@'127.0.0.1' | def           | CREATE ROUTINE          | YES          |
| 'root'@'::1'       | def           | CREATE TABLESPACE       | YES          |
| 'root'@'localhost' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TABLESPACE       | YES          |
| 'root'@'::1'       | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'127.0.0.1' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost' | def           | CREATE USER             | YES          |
| 'root'@'127.0.0.1' | def           | CREATE USER             | YES          |
| 'root'@'::1'       | def           | CREATE USER             | YES          |
| 'root'@'localhost' | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | CREATE VIEW             | YES          |
| 'root'@'::1'       | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1' | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | DELETE                  | YES          |
| 'root'@'localhost' | def           | DELETE                  | YES          |
| 'root'@'::1'       | def           | DROP                    | YES          |
| 'root'@'localhost' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | DROP                    | YES          |
| 'root'@'127.0.0.1' | def           | EVENT                   | YES          |
| 'root'@'::1'       | def           | EVENT                   | YES          |
| 'root'@'localhost' | def           | EVENT                   | YES          |
| 'root'@'127.0.0.1' | def           | EXECUTE                 | YES          |
| 'root'@'::1'       | def           | EXECUTE                 | YES          |
| 'root'@'localhost' | def           | EXECUTE                 | YES          |
| 'root'@'127.0.0.1' | def           | FILE                    | YES          |
| 'root'@'::1'       | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | FILE                    | YES          |
| 'root'@'localhost' | def           | INDEX                   | YES          |
| 'root'@'127.0.0.1' | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | INDEX                   | YES          |
| 'root'@'::1'       | def           | INSERT                  | YES          |
| 'root'@'localhost' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1' | def           | LOCK TABLES             | YES          |
| 'root'@'::1'       | def           | LOCK TABLES             | YES          |
| 'root'@'localhost' | def           | LOCK TABLES             | YES          |
| 'root'@'127.0.0.1' | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | PROCESS                 | YES          |
| 'root'@'localhost' | def           | PROCESS                 | YES          |
| 'root'@'::1'       | def           | REFERENCES              | YES          |
| 'root'@'localhost' | def           | REFERENCES              | YES          |
| 'root'@'127.0.0.1' | def           | REFERENCES              | YES          |
| 'root'@'::1'       | def           | RELOAD                  | YES          |
| 'root'@'localhost' | def           | RELOAD                  | YES          |
| 'root'@'127.0.0.1' | def           | RELOAD                  | YES          |
| 'root'@'::1'       | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'::1'       | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1' | def           | SELECT                  | YES          |
| 'root'@'::1'       | def           | SELECT                  | YES          |
| 'root'@'localhost' | def           | SELECT                  | YES          |
| 'root'@'127.0.0.1' | def           | SHOW DATABASES          |  YES          |
| 'root'@'::1'       | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost' | def           | SHOW DATABASES          | YES          |
| 'root'@'127.0.0.1' | def           | SHOW VIEW               | YES          |
| 'root'@'::1'       | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | SHOW VIEW               | YES          |
| 'root'@'localhost' | def           | SHUTDOWN                | YES          |
| 'root'@'127.0.0.1' | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | SHUTDOWN                | YES          |
| 'root'@'::1'       | def           | SUPER                   | YES          |
| 'root'@'localhost' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1' | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | TRIGGER                 | YES          |
| 'root'@'localhost' | def           | TRIGGER                 | YES          |
| 'root'@'::1'       | def           | UPDATE                  | YES          |
| 'root'@'localhost' | def           | UPDATE                  | YES          |
| 'root'@'127.0.0.1' | def           | UPDATE                  | YES          |
| ''@'localhost'     | def           | USAGE                   | NO           |     +--------------------+---------------+-------------------------+--------------+
85 rows in set (0.00 sec)

DEFINITIVELY, a long answer and alot of scrolling. Also I struggled hard to pass the output of the queries to a text file. Here is how to do that without using the annoying into outfile thing-

肯定地说，答案很长，而且有很多滚动条。此外，我还努力将查询的输出传递给文本文件。这里是如何做到这一点，而不使用恼人的进入外文件的东西-

tee E:/sqllogfile.txt;

t E:/ sqllogfile.txt;

And when you are done, stop the logging-

完成后，停止日志记录-

tee off;

发球;

Hope it adds more clarity.

希望它能增加清晰度。