Sorting with ORDER BY
Databases & SQL Lesson 5 1:59 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Rows come back in no particular order.
行回来的时候是没有特定顺序的。
It usually LOOKS like insertion order, but nothing promises that, and a database is free to change it.
它通常"看起来"像是插入的顺序，但没有任何东西保证这一点， 数据库随时可以改变它。
ORDER BY is how you actually ask.
ORDER BY 才是你真正提出要求的方式。
Sort by score descending and the result is largest first, top mark at the top — which is what a report needs.
按 score 降序排，结果就是最大的在最前面，最高分在最上面—— 这正是一份报表需要的。
Two directions.
两个方向。
ASC is ascending, smallest first, and it is the default — so the first and second queries here do exactly the same thing.
ASC 是升序，最小的在前，而且它是默认的—— 所以这里第一条和第二条查询做的完全是同一件事。
DESC is descending, largest first, and you must ask for it by name.
DESC 是降序，最大的在前，你必须指名要它。
On a text column ascending means A to Z, so sorting names alphabetically needs no keyword at all.
在文字列上，升序意味着从 A 到 Z， 所以把名字按字母顺序排，一个关键字都不用加。
You can sort by more than one column, and this is the part worth watching.
你可以按不止一列来排序，而这是值得看清楚的部分。
Form ascending puts all the 11A rows first.
form 升序把所有 11A 的行放在前面。
But three students share that form, so the first column has nothing left to say about their order — and the second one decides it, sorting them by score within that group.
但有三个学生同属这个班， 所以第一列对它们之间的顺序已经无话可说了—— 于是第二列来决定，在这个组内部按 score 排。
First column groups, second column breaks the tie.
第一列负责分组，第二列负责打破平局。
The clauses have a fixed order and ORDER BY is the last of them: SELECT, then FROM, then WHERE, then ORDER BY.
各个子句有固定的顺序，而 ORDER BY 是最后一个： 先 SELECT，再 FROM，再 WHERE，最后 ORDER BY。
That order is also the sensible one to think in — filter first, and sort what is left.
这个顺序也正是思考时该用的顺序——先过滤，再对剩下的排序。
A database that sorted first would spend its time sorting rows it is about to throw away.
一个先排序的数据库，会把时间花在给马上要扔掉的行排序上。
Four things to take with you.
带走四点。
One: without ORDER BY the row order is not promised.
第一：不写 ORDER BY，行的顺序就没有保证。
Two: ASC is the default, so add DESC for largest first.
第二：ASC 是默认的，要最大的在前就加 DESC。
Three: a second column decides ties within the first.
第三：第二列在第一列相同时决定顺序。
Four: ORDER BY is the last clause in the query.
第四：ORDER BY 是查询里的最后一个子句。
Now run the tasks below.
现在去做下面的题。