MySQL记录
MySQL记录
用户名 ：nipper（net start nipper，net stop nipper）或者在计算机管理中->服务和应用程序->服务中 找到nipper打开
- 需要使用管理员模式打开cmd win+R 然后 shift+ctrl+enter
代码
题目
构造表如下
mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 10001 | d001 | 1986-06-26 | 9999-01-01 |
| 10002 | d001 | 1996-08-03 | 9999-01-01 |
| 10003 | d004 | 1995-12-03 | 9999-01-01 |
| 10004 | d004 | 1986-12-01 | 9999-01-01 |
| 10005 | d003 | 1989-09-12 | 9999-01-01 |
| 10006 | d002 | 1990-08-05 | 9999-01-01 |
| 10007 | d005 | 1989-02-10 | 9999-01-01 |
| 10009 | d006 | 1985-02-18 | 9999-01-01 |
| 10010 | d006 | 2000-06-26 | 9999-01-01 |
+--------+---------+------------+------------+
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 88958 | 2002-06-22 | 9999-01-01 |
| 10002 | 72527 | 2001-08-02 | 9999-01-01 |
| 10003 | 43311 | 2001-12-01 | 9999-01-01 |
| 10004 | 74057 | 2001-11-27 | 9999-01-01 |
| 10005 | 94692 | 2001-09-09 | 9999-01-01 |
| 10006 | 43311 | 2001-08-02 | 9999-01-01 |
| 10007 | 88070 | 2002-02-07 | 9999-01-01 |
| 10009 | 95409 | 2002-02-14 | 9999-01-01 |
| 10010 | 94409 | 2001-11-23 | 9999-01-01 |
+--------+--------+------------+------------+
需要连接两表：
mysql> select d.dept_no,d.emp_no,s.salary
-> from dept_emp d, salaries s
-> where d.emp_no=s.emp_no;
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001 | 10001 | 88958 |
| d001 | 10002 | 72527 |
| d004 | 10003 | 43311 |
| d004 | 10004 | 74057 |
| d003 | 10005 | 94692 |
| d002 | 10006 | 43311 |
| d005 | 10007 | 88070 |
| d006 | 10009 | 95409 |
| d006 | 10010 | 94409 |
+---------+--------+--------+
mysql> select d.dept_no,s.emp_no,s.salary
-> from dept_emp d inner join salaries s
-> on d.emp_no=s.emp_no
-> group by d.dept_no
-> ;
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001 | 10001 | 88958 |
| d004 | 10003 | 43311 |
| d003 | 10005 | 94692 |
| d002 | 10006 | 43311 |
| d005 | 10007 | 88070 |
| d006 | 10009 | 95409 |
+---------+--------+--------+
mysql> select d.dept_no,d.emp_no,max(s.salary) salary
-> from dept_emp d, salaries s
-> where d.emp_no=s.emp_no
-> group by d.dept_no;
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001 | 10001 | 88958 |
| d004 | 10003 | 74057 |
| d003 | 10005 | 94692 |
| d002 | 10006 | 43311 |
| d005 | 10007 | 88070 |
| d006 | 10009 | 95409 |
+---------+--------+--------+
# 通过group by分组，得到的max(s.salary)与前两项不匹配，
# group by分组得到的dept_no和emp_no都没有变，只把max(salary)变了
# ！！！！对比一下这个和上一个！！！！
mysql> select d.dept_no,s.emp_no,max(s.salary)
-> from dept_emp d, salaries s
-> where d.emp_no=s.emp_no
-> ;
+---------+--------+---------------+
| dept_no | emp_no | max(s.salary) |
+---------+--------+---------------+
| d001 | 10001 | 95409 |
+---------+--------+---------------+
发现max(salary)是独立的，并不会提取max(salary)相对应的哪一行。
解决办法：
使用partition by，这也是分组，但group by是聚合分组，即一组数据最后得出了一个值
而partition by可以将分组后的数据都显示出来
代码如下：
mysql> SELECT dept_no,emp_no,salary
-> FROM (
-> SELECT dept_no,salary,d.emp_no,
-> row_number() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS a
-> FROM dept_emp d
-> INNer JOIN salaries s
-> ON d.emp_no= s.emp_no) b
-> WHERE a =1;
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001 | 10001 | 88958 |
| d002 | 10006 | 43311 |
| d003 | 10005 | 94692 |
| d004 | 10004 | 74057 |
| d005 | 10007 | 88070 |
| d006 | 10009 | 95409 |
+---------+--------+--------+
6 rows in set (0.00 sec)
# 分解步骤：
mysql> SELECT dept_no,salary,d.emp_no,
-> row_number() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS a
-> FROM dept_emp d
-> INNer JOIN salaries s
-> ON d.emp_no= s.emp_no;
+---------+--------+--------+---+
| dept_no | salary | emp_no | a |
+---------+--------+--------+---+
| d001 | 88958 | 10001 | 1 |
| d001 | 72527 | 10002 | 2 |
| d002 | 43311 | 10006 | 1 |
| d003 | 94692 | 10005 | 1 |
| d004 | 74057 | 10004 | 1 |
| d004 | 43311 | 10003 | 2 |
| d005 | 88070 | 10007 | 1 |
| d006 | 95409 | 10009 | 1 |
| d006 | 94409 | 10010 | 2 |
+---------+--------+--------+---+
把row_number()替换成max()函数试试~
mysql> SELECT dept_no,salary,d.emp_no,
-> max(salary) OVER (PARTITION BY dept_no ORDER BY salary DESC) AS a
-> FROM dept_emp d
-> INNer JOIN salaries s
-> ON d.emp_no= s.emp_no;
+---------+--------+--------+-------+
| dept_no | salary | emp_no | a |
+---------+--------+--------+-------+
| d001 | 88958 | 10001 | 88958 |
| d001 | 72527 | 10002 | 88958 |
| d002 | 43311 | 10006 | 43311 |
| d003 | 94692 | 10005 | 94692 |
| d004 | 74057 | 10004 | 74057 |
| d004 | 43311 | 10003 | 74057 |
| d005 | 88070 | 10007 | 88070 |
| d006 | 95409 | 10009 | 95409 |
| d006 | 94409 | 10010 | 95409 |
+---------+--------+--------+-------+
9 rows in set (0.00 sec)
知识点
row_number(),dense_rank(),rank()都是排序，但是比如100，100，200排序
row_number()排序结果为1，2，3（正常排序）
dense_rank()排序结果为1，1，2（总量变）
rank() 排序结果为1，1，3（总量不变）
