MYSQL 操作顺序失败 : ORDER BY is affecting the results of SELECT Statement

Question

我正在尝试使用 MySQL 在 HackerRank 上解决这个问题。 https://www.hackerrank.com/challenges/occupations

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should Doctor, Professor, Singer, and Actor, respectively.

#Input Format

Samantha Doctor
Jenny Doctor
Ashley Professor
...

#Sample Output

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

这是针对 MYSQL 给出的解决方案:

set @r1=0, @r2=0, @r3=0, @r4=0;
select min(Doctor), min(Professor), min(Singer), min(Actor) from(
    select case when Occupation='Doctor' then (@r1:=@r1+1)
        when Occupation='Professor' then (@r2:=@r2+1)
        when Occupation='Singer' then (@r3:=@r3+1)
        when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
    case when Occupation='Doctor' then Name end as Doctor,
    case when Occupation='Professor' then Name end as Professor,
    case when Occupation='Singer' then Name end as Singer,
    case when Occupation='Actor' then Name end as Actor
from OCCUPATIONS
order by Name
) Temp
group by RowNumber

这将按字母顺序返回输出，而不是随机顺序，因为 RowNumber 已因 ORDER BY 语句而更改。

我的问题是，为什么按名称排序会影响列 RowNumber 的排序？

SQL 中的操作顺序表明所有 SELECT 语句都发生在 ORDER BY 语句之前。 ORDER BY 的时候不应该已经计算出行号了吗？

编辑:
由于人们在询问操作顺序，我已经包含了我见过的多个地方的链接(更不用说我的 SQL 教科书了): http://sqlbolt.com/lesson/select_queries_order_of_execution
http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

Answer 1

首先，哪里是按照操作顺序表示 SELECT 的在 ORDER BY 之前执行 ？这种说法根本不正确。

是的事实是 SELECT在 ORDER BY 之前由编译器求值 .为此，SELECT 中定义的列别名可用于 ORDER BY .

除此之外，变量是 SQL 的扩展。 MySQL很明显SELECT是执行的最后一个子句:

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected . . .

因为 ORDER BY发生在结果返回给客户端之前，数据在计算带有变量的表达式之前被排序。