1.排序。
此处说的排序主要指使用窗口函数中的排序,分别为dense_rank() over(partition by ... order by ...),rank() over(partition by ... order by ...), row_number() over(partition by ... order by ...),并非order by。
那什么时候会用到排序呢?有的题目本身就是为了得到排序结果,这样就比较简单,例如1.部门工资前三高的所有员工
dense_rank() over(partition by DepartmentId order by salary desc)
row_number() over(partition by username order by startDate desc)
row_number() over(partition by seller_id order by order_date)
row_number() over(partition by p.group_id order by (sum(if(p.player_id=m.first_player, m.first_score, m.second_score)) desc;
而有些题目则可以通过排序,找出中间态进而得到答案,包括
row_number() over(partition by company order by salary)
row_number() over (partition by continent order by name)
dense_rank() over (PARTITION BY caller_id, DATE_FORMAT(call_time, '%Y-%m-%d') order by call_time ASC)
dense_rank() over (PARTITION BY caller_id, DATE_FORMAT(call_time, '%Y-%m-%d') order by call_time DESC)
总体来说,使用窗口函数排序,可复杂问题简单化。
2.创建聚合键
当题目中并没有明确的聚合键而又不得不用才能进展下去时,就要再自己创建了。便可以使用窗口函数中的排序来完成,其实和上边的类型是一样的,只不过这种方法很巧妙故单独来出来。包括
id-row_number() over(order by id)
t2.rn-row_number() over(partition by t2.flag order by t2.dt)
3.日期类型变化
当原数据日期类型并不能直接使用时,那就要更改类型了。其中第一个函数可以使用date_format()函数用于以不用的格式显示日期时间等,包括
date_format(s.pay_date, "%Y-%m")
date_format(call_time, '%Y-%m-%d')
第二个函数可以使用weekday,获取的是指定日期的星期
weekday(o.order_date)
4.(not) in和(not) exists
in:在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候,in的速度较快。包括
where r in (...)
exists:指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。相对外表比较小的时候,exist是的速度较快。
not in:查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引9.查找成绩处于中游的学生
where e.student_id not in (...)
not exist:not extsts 的子查询依然能用到表上的索引。
where e.student_id not in (...)
and not exists (...)
也就是说,in和exists需要具体情况具体分析,not in和not exists就不用分析了,尽量用not exists就好了。
5.联结方式
说的联结方式并不是说某种确定联结方式,想表达的是比如说两张表联结时,on中不只是table1.id = table2.id这么简单,还可以
on t.client_id = u1.users_id and u1.banned = "No"
on (t.id = e1.id and t.maxmonth > e1.month)
ON (a.player_id = b.player_id AND DATEDIFF(b.event_date , a.first_date) = 1)
on (m.first_player = p.player_id or m.second_player = p.player_id)
on a.user_id <> b.user_id
ON (a.caller_id = b.caller_id AND a.recipient_id = b.recipient_id AND a.rk = 1 AND b.rk = 1)
其实就可以理解为精确的联结,这样才不会多或少行。
6.笛卡尔集(cross join)
相比于inner join、left join、right join, cross join用的少甚至还要避免这种操作,但是在有些时候没有笛卡尔集的情况下就没办法全部显示,比如
a cross join b
所以说学知识点的时候要灵活,学会发散。