EG：取出没门课程的第一名：
CREATE TABLE window_test
(id int,
name text,
subject text,
score numeric
);
INSERT INTO window_test VALUES (1,'小黄','数学',99.5), (2,'小黄','语文',89.5),(3,'小黄','英语',79.5), (4,'小黄','物理',99.5), (5,'小黄','化学',98.5), (6,'小红','数学',89.5), (7,'小红','语文',99.5), (8,'小红','英语',79.5), (9,'小红','物理',89.5), (10,'小红','化学',69.5),(11,'小绿','数学',89.5), (12,'小绿','语文',91.5), (13,'小绿','英语',92.5),(14,'小绿','物理',93.5), (15,'小绿','化学',94.5);
root@my3308.sock-8.0.11>[employees]>select * from window_test;
+------+--------+---------+-------+
| id | name | subject | score |
+------+--------+---------+-------+
| 1 | 小黄 | 数学 | 100 |
| 2 | 小黄 | 语文 | 90 |
| 3 | 小黄 | 英语 | 80 |
| 4 | 小黄 | 物理 | 100 |
| 5 | 小黄 | 化学 | 99 |
| 6 | 小红 | 数学 | 90 |
| 7 | 小红 | 语文 | 100 |
| 8 | 小红 | 英语 | 80 |
| 9 | 小红 | 物理 | 90 |
| 10 | 小红 | 化学 | 70 |
| 11 | 小绿 | 数学 | 90 |
| 12 | 小绿 | 语文 | 92 |
| 13 | 小绿 | 英语 | 93 |
| 14 | 小绿 | 物理 | 94 |
| 15 | 小绿 | 化学 | 95 |
+------+--------+---------+-------+
15 rows in set (0.00 sec)直接写成如下形式：发现结果并不对。如何解决呢？
root@my3308.sock-8.0.11>[employees]>select id,name,subject,max(score) from window_test group by subject;
+------+--------+---------+------------+
| id | name | subject | max(score) |
+------+--------+---------+------------+
| 1 | 小黄 | 数学 | 100 |
| 2 | 小黄 | 语文 | 100 |
| 3 | 小黄 | 英语 | 93 |
| 4 | 小黄 | 物理 | 100 |
| 5 | 小黄 | 化学 | 99 |
+------+--------+---------+------------+
5 rows in set (0.00 sec)1.当没有窗口函数之前我们使用哪几种方法：
方法一：使用jion 形式
a.按照subject 分组，取出score 的最大值
root@my3308.sock-8.0.11>[employees]>select subject,max(score) from window_test group by subject;
+---------+------------+
| subject | max(score) |
+---------+------------+
| 数学 | 100 |
| 语文 | 100 |
| 英语 | 93 |
| 物理 | 100 |
| 化学 | 99 |
+---------+------------+
5 rows in set (0.00 sec)b.根据subject,score 的值再去关键Window_test表取出其他几列的值
root@my3308.sock-8.0.11>[employees]>select b.* from
(select subject,max(score) as score from window_test group by subject
) a
join window_test b on a.score = b.score and a.subject = b.subject;
+------+--------+---------+-------+
| id | name | subject | score |
+------+--------+---------+-------+
| 1 | 小黄 | 数学 | 100 |
| 4 | 小黄 | 物理 | 100 |
| 5 | 小黄 | 化学 | 99 |
| 7 | 小红 | 语文 | 100 |
| 13 | 小绿 | 英语 | 93 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)
此时的结果就是证券的了。方法二：例如group by 特性来写
a.window_test 按照subject asc,score desc 来排序
root@my3308.sock-8.0.11>[employees]>select * from window_test order by subject, score desc;
+------+--------+---------+-------+
| id | name | subject | score |
+------+--------+---------+-------+
| 5 | 小黄 | 化学 | 99 |
| 15 | 小绿 | 化学 | 95 |
| 10 | 小红 | 化学 | 70 |
| 1 | 小黄 | 数学 | 100 |
| 6 | 小红 | 数学 | 90 |
| 11 | 小绿 | 数学 | 90 |
| 4 | 小黄 | 物理 | 100 |
| 14 | 小绿 | 物理 | 94 |
| 9 | 小红 | 物理 | 90 |
| 13 | 小绿 | 英语 | 93 |
| 3 | 小黄 | 英语 | 80 |
| 8 | 小红 | 英语 | 80 |
| 7 | 小红 | 语文 | 100 |
| 12 | 小绿 | 语文 | 92 |
| 2 | 小黄 | 语文 | 90 |
+------+--------+---------+-------+
15 rows in set (0.00 sec)b.进行对此结果进行group by
root@my3308.sock-8.0.11>[employees]>select * from (select * from window_test order by subject, score desc) a group by a.subject;
+------+--------+---------+-------+
| id | name | subject | score |
+------+--------+---------+-------+
| 1 | 小黄 | 数学 | 100 |
| 2 | 小黄 | 语文 | 90 |
| 3 | 小黄 | 英语 | 80 |
| 4 | 小黄 | 物理 | 100 |
| 5 | 小黄 | 化学 | 99 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)有没有注意到此时的结果是不正确的，那么我们来看下具体的执行计划。哪里出错了呢？
root@my3308.sock-8.0.11>[employees]>explain select * from (select * from window_test order by subject, score desc) a group by a.subject;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: window_test
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 15
filtered: 100.00
Extra: Using temporary
1 row in set, 1 warning (0.00 sec)
从执行计划中我们得到了什么？有没有发现，视图进行了合并了。explain select_type = simple 。那么我们其实可以通过show warnings\G来查看具体的mysql 改写的语句
root@my3308.sock-8.0.11>[employees]>show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */
select `employees`.`window_test`.`id` AS `id`,
`employees`.`window_test`.`name` AS `name`,
`employees`.`window_test`.`subject` AS `subject`,
`employees`.`window_test`.`score` AS `score`
from `employees`.`window_test` group by `employees`.`window_test`.`subject`
1 row in set (0.00 sec)
可以直接从show warnings很直观的看到mysql 优化器给改写的SQL。
而在5.6上没有进行合并，5.7 8.0上进行了视图合并，我们应该如何避免呢？
1.5.7优化器新添加了什么功能造成视图合并？
2.如进行避免视图合并有哪几种方法：
方法一：
root@my3308.sock-8.0.11>[employees]>set optimizer_switch ="derived_merge=off";
Query OK, 0 rows affected (0.00 sec)直接将derived_merge 进行关闭了。这种方法太暴力了。。。。非常不推荐，那么我们应该使用什么方法来解决此问题呢？大家自己思考下
root@my3308.sock-8.0.11>[employees]>set optimizer_switch ="derived_merge=off";
Query OK, 0 rows affected (0.00 sec)
root@my3308.sock-8.0.11>[employees]>select * from (select * from window_test order by subject, score desc) a group by a.subject;
+------+--------+---------+-------+
| id | name | subject | score |
+------+--------+---------+-------+
| 5 | 小黄 | 化学 | 99 |
| 1 | 小黄 | 数学 | 100 |
| 4 | 小黄 | 物理 | 100 |
| 13 | 小绿 | 英语 | 93 |
| 7 | 小红 | 语文 | 100 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)
root@my3308.sock-8.0.11>[employees]>explain select * from (select * from window_test order by subject, score desc) a group by a.subject;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using temporary |
| 2 | DERIVED | window_test | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)
此时结果就对了方法三：使用if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) 形式来模拟
a.使用if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) 来进行subject 分组，以及显示各个row_num
root@my3308.sock-8.0.11>[employees]>select t.*,if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) as rn ,@_subject:=t.subject as _subject
-> from(select t.* from window_test t order by subject,score desc ) t , (select @rn:=0 rn ,@_subject:='') b;
+------+--------+---------+-------+------+----------+
| id | name | subject | score | rn | _subject |
+------+--------+---------+-------+------+----------+
| 5 | 小黄 | 化学 | 99 | 1 | 化学 |
| 15 | 小绿 | 化学 | 95 | 2 | 化学 |
| 10 | 小红 | 化学 | 70 | 3 | 化学 |
| 1 | 小黄 | 数学 | 100 | 1 | 数学 |
| 6 | 小红 | 数学 | 90 | 2 | 数学 |
| 11 | 小绿 | 数学 | 90 | 3 | 数学 |
| 4 | 小黄 | 物理 | 100 | 1 | 物理 |
| 14 | 小绿 | 物理 | 94 | 2 | 物理 |
| 9 | 小红 | 物理 | 90 | 3 | 物理 |
| 13 | 小绿 | 英语 | 93 | 1 | 英语 |
| 3 | 小黄 | 英语 | 80 | 2 | 英语 |
| 8 | 小红 | 英语 | 80 | 3 | 英语 |
| 7 | 小红 | 语文 | 100 | 1 | 语文 |
| 12 | 小绿 | 语文 | 92 | 2 | 语文 |
| 2 | 小黄 | 语文 | 90 | 3 | 语文 |
+------+--------+---------+-------+------+----------+
15 rows in set (0.00 sec)b.进行取出rn = 1 的结果
root@my3308.sock-8.0.11>[employees]>select x.id,x.name,x.subject,x.score
-> from (
-> select t.*,if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) as rn ,@_subject:=t.subject as _subject
-> from(select t.* from window_test t order by subject,score desc ) t , (select @rn:=0 rn ,@_subject:='') b
-> )x WHERE rn=1 ORDER BY SUBJECT;
+------+--------+---------+-------+
| id | name | subject | score |
+------+--------+---------+-------+
| 5 | 小黄 | 化学 | 99 |
| 1 | 小黄 | 数学 | 100 |
| 4 | 小黄 | 物理 | 100 |
| 13 | 小绿 | 英语 | 93 |
| 7 | 小红 | 语文 | 100 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)方法四：使用8.0引入的窗口函数row_number over..
root@my3308.sock-8.0.11>[employees]>select id,name,subject,score from (
-> select row_number()over(partition by subject order by score desc) as rn,
-> id,name,subject,score
-> from window_test )t
-> where rn=1;
+------+--------+---------+-------+
| id | name | subject | score |
+------+--------+---------+-------+
| 5 | 小黄 | 化学 | 99 |
| 1 | 小黄 | 数学 | 100 |
| 4 | 小黄 | 物理 | 100 |
| 13 | 小绿 | 英语 | 93 |
| 7 | 小红 | 语文 | 100 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)
是不是很方便。
root@my3308.sock-8.0.11>[employees]>with cte as (
-> select row_number()over(partition by subject order by score desc) as rn,
-> id,name,subject,score
-> from window_test
-> )select id,name,subject,score from cte where rn = 1;
+------+--------+---------+-------+
| id | name | subject | score |
+------+--------+---------+-------+
| 5 | 小黄 | 化学 | 99 |
| 1 | 小黄 | 数学 | 100 |
| 4 | 小黄 | 物理 | 100 |
| 13 | 小绿 | 英语 | 93 |
| 7 | 小红 | 语文 | 100 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)