sql_order

mysql中order by 的疑惑

1.sql中写的顺序

select
from
where
group by 
having
order by

2.执行的顺序

from
where
group by 
having
select
order by 

3.当自定义变量时，好像order by会对变量进行改变

3.1原表

3.2假设给科目排名，并列就相同名次。没有order by时，不是我们想要的结果

select
sc.*,
case 
when @last = sc.CId then @nums
when @last := sc.CId then @nums := @nums + 1
end as 排名
from SC sc,(select @nums := 0,@last := 0) r

3.3添加order by时，这个是我们想要的结果，但是order by应该在select后面执行

select
sc.*,
case 
when @last = sc.CId then @nums
when @last := sc.CId then @nums := @nums + 1
end as 排名
from SC sc,(select @nums := 0,@last := 0) r
order by sc.CId

3.4换个常规写法，相当于排序之后，再算

select
sc.*,
case 
when @last = sc.CId then @nums
when @last := sc.CId then @nums := @nums + 1
end as 排名
from (
			select * 
			from SC 
			order by CId desc
) sc,(select @nums := 0,@last := 0) r
order by sc.CId

4 第一种（3.3）比第二种（3.4）快，因为3.4生成了临时表有两次from

5 为啥order by影响了select