SQL执行顺序和coalesce以及case when的用法

1、mysql的执行顺序 

from ->  on -> join -> where ->  group by  -> 聚集函数  -> having ->  select  ->distinct -> union -> order by -> limit

注意：select distinct name from t1 能消除重复记录，但只能取一个字段，现在要同时取id,name这2个字段的值。 

　　  select distinct id,name from t1 可以取多个字段，但只能消除这2个字段值全部相同的记录 

　　　limit y 分句表示: 读取 y 条数据
　　　limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
　　　limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

　　　limit n,m表示从第n+1条开始,取m条.limit 2,3表示取3,4,5条.

表teacher（id,dept,name,phone,mobile）

表dept（id,name）

teacher.dept=dept.id 

2、SQL中coalesce函数的用法（也相当于HiveQL中nvl函数的用法）：

　　其参数格式如下： COALESCE ( expression,value1,value2……,valuen) 

　　COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。

　　COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。如果
　　expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返
　　回value1；否则判断value2是否是空值，如果value2不为空值则返回value3；……以此类推，

　　例1：Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'

　　select name,COALESCE(mobile,'07986 444 2266')
　　from teacher

　　结果如下：

　　　　

3、case when的用法：

　　例1：

　　表user（id,name,sex）

　　select u.id,u.name,u.sex,
　　(case u.sex
　　　　when 1 then '男'
　　　　when 2 then '女'
　　　　else '空的'
　　end
　　)性别
　　from user u;

　　例2：表game（id，mdate，stadium，team1，team2）

　　　　 表goal（matchid，teamid，player，gtime）

　　　　查询中列出了每个目标。如果是team1的进球，那么1出现在score1中，否则为0。您可以对这一列进行求和，以得到team1进球的总数。

　　SELECT mdate,

　　　　team1,

　　　　SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,　　//SUM(CASE teamid WHEN team1 THEN 1 ELSE 0 END) score1,

　　　　team2,

　　　　SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2　　//SUM(CASE teamid WHEN team2 THEN 1 ELSE 0 END) score2

　　　　FROM game LEFT JOIN goal ON game.id = matchid

　　group by mdate,team1,team2
　　结果如下：

　　

4、case when 的列转行

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

方法一（正确答案）

select S ,max(case C when '01' then score else 1 end) '01',
max(case C when '02' then score else 0 end)'02',
max(case C when '03' then score else 0 end)'03',AVG(score)平均分 from sc
group by S order by AVG(score) desc;

比较

select S ,sum(case C when '01' then score else 1 end) '01',
sum(case C when '02' then score else 0 end)'02',
sum(case C when '03' then score else 0 end)'03',AVG(score)平均分 from sc
group by S order by AVG(score) desc;

数据

 

解析：方法一：首先按照学号分组，第一行C=01，第二行C=02，第三行C=03，故学号为07的学生第二个字段为MAX（0，0，0）同理之后的字段也是max(0，89，0)和max（0，0，98）.

　　比较语句：学号为01的学生的第二个字段为sum(0，1，1)=2。以此类推。

结果分别与下图所示：