关于MySQL多字段排序

Question

这是emp表

给两个字段排序

好奇怪啊好奇怪

Answer 1

第二个sql，按empno升序，如果empno相同就按salary降序，没毛病啊

Answer 2

奇怪点在哪呢？？？

Answer 3

MySQL is a relational database query language. The ORDER BY Keyword in MySQL is designed to sort all the relations in the table and then return them in 1 (usually 1 but with edge cases) specific order. Each relation has a priority which is determined by the keywords followed by ORDER BY. Only when the priorities of two relations break even in the first keyword, the significance of the second keyword comes into place. So on and so force, if all the keywords are used and the priority in all keywords make a tie, then the relations are returned in a default order.

MySQL是一种关系数据库的查询指令集语言。在 MySQL中ORDERBY关键词用来对数据表中的所有关系进行排序，然后在1种（往往是1种也有特例）特定的顺序下返回所有的关系。 每个查询结果关系有一个优先级。紧跟着ORDERBY的关键词可以决定优先级。当且仅当两个关系的优先级用第一个关键词难分上下的时候，第二个关键词的重要性才体现出来。假如第二个依旧平手的话，就看第三个。假如所有的关键词逗被用过了，还是几个关系的优先级还是平手，那么它们的返回顺序是默认顺序。

In your case, the first query asks for a negatively ranked salary for each department, which makes sense. The second query asks for a negatively ranked salary for each employee, which does not make sense. The emp table is contructed in such a way that 1 employee only has 1 salary entry. Sorting an employee's salary is not meaningful. Instead what you can do is find out the employee who earns the most using the following MySQL query.

SELECT deptno, empname, salary FROM emp ORDER BY salary

针对你的问题，第一个查询语句查找了每个部门员工薪资的倒数排名，有理可依。第二个查询语句查找了每一个员工的薪资的倒数排名，没有道理啊。数据表emp被创建的时候，一个员工只能输入一个薪资，排序一个员工自身的薪资是没有意义的。作为替代，你可以找到薪资最高的员工，用以下的MySQL查询语句。

SELECT deptno, empname, salary FROM emp ORDER BY salary