SQL select 语句中 Order By 1 的目的是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3445118/
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
What is the purpose of Order By 1 in SQL select statement?
提问by echo
I'm reading through some old code at work, and have noticed that there are several views with an order by 1clause. 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;
回答by OMG Ponies
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:
这不是推荐的做法，因为：
- It's not obvious/explicit
- If the column order changes, the query is still valid so you risk ordering by something you didn't intend
- 这不明显/明确
- 如果列顺序发生变化，查询仍然有效，因此您可能会按您不希望的顺序进行排序
回答by daven11
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;
回答by kshitij
it simply means sorting the view or table by 1st column of query's result.
它只是意味着按查询结果的第一列对视图或表进行排序。
回答by Christoph
I believe in Oracle it means order by column #1
我相信 Oracle 这意味着按列 #1 排序
回答by CTKeane
This will sort your results by the first column returned. In the example it will sort by payment_date.
这将按返回的第一列对结果进行排序。在示例中，它将按支付日期排序。
回答by nicV
As mentioned in other answers ORDER BY 1orders by the first column.
如ORDER BY 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 Namein the below.
我遇到了另一个可以使用它的例子。我们有某些需要排序的查询选择相同的列。如果按Name以下顺序排序，您将收到 SQL 错误。
SELECT Name, Name FROM Segment ORDER BY 1
回答by kriss
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_TYPEor can be given order by 3. Notice the 3rd column (PRIVILEGE_TYPE) getting sorted alphabetically.
而当它被额外给予order by PRIVILEGE_TYPE或可以给予order by 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 outfilething-
最终，一个很长的答案和大量的滚动。我也努力将查询的输出传递到文本文件。这是不使用烦人的东西的方法into outfile-
tee E:/sqllogfile.txt;
三通E:/sqllogfile.txt;
And when you are done, stop the logging-
完成后，停止记录-
tee off;
开球；
Hope it adds more clarity.
希望它增加了更多的清晰度。
回答by kdmurray
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! :)
有关 order by 的说明。我学到了一些东西！:)
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
