• SQL练习题

    新建数据表

    首先建立测试数据库的表，新建数据库的sql语句如下，大家可以粘贴成一个sql文件，然后新建所有的表并插入所有的数据：

    新建数据库sql文件：

    1. DROP TABLE IF EXISTS EMP;
    2. DROP TABLE IF EXISTS DEPT;
    3. DROP TABLE IF EXISTS SALGRADE;
    4.  
    5. CREATE TABLE DEPT
    6.        (DEPTNO int(2) not null ,
    7. 	DNAME VARCHAR(14) ,
    8. 	LOC VARCHAR(13),
    9. 	primary key (DEPTNO)
    10. 	);
    11. CREATE TABLE EMP
    12.        (EMPNO int(4)  not null ,
    13. 	ENAME VARCHAR(10),
    14. 	JOB VARCHAR(9),
    15. 	MGR INT(4),
    16. 	HIREDATE DATE  DEFAULT NULL,
    17. 	SAL DOUBLE(7,2),
    18. 	COMM DOUBLE(7,2),
    19. 	primary key (EMPNO),
    20. 	DEPTNO INT(2) 
    21. 	)
    22. 	;
    23.  
    24. CREATE TABLE SALGRADE
    25.       ( GRADE INT,
    26. 	LOSAL INT,
    27. 	HISAL INT );
    28.  
    29.  
    30.  
    31.  
    32. INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    33. 10, 'ACCOUNTING', 'NEW YORK'); 
    34. INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    35. 20, 'RESEARCH', 'DALLAS'); 
    36. INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    37. 30, 'SALES', 'CHICAGO'); 
    38. INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    39. 40, 'OPERATIONS', 'BOSTON'); 
    40. commit;
    41.  
    42. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    43. DEPTNO ) VALUES ( 
    44. 7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
    45. , 800, NULL, 20); 
    46. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    47. DEPTNO ) VALUES ( 
    48. 7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
    49. , 1600, 300, 30); 
    50. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    51. DEPTNO ) VALUES ( 
    52. 7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
    53. , 1250, 500, 30); 
    54. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    55. DEPTNO ) VALUES ( 
    56. 7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
    57. , 2975, NULL, 20); 
    58. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    59. DEPTNO ) VALUES ( 
    60. 7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
    61. , 1250, 1400, 30); 
    62. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    63. DEPTNO ) VALUES ( 
    64. 7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
    65. , 2850, NULL, 30); 
    66. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    67. DEPTNO ) VALUES ( 
    68. 7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
    69. , 2450, NULL, 10); 
    70. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    71. DEPTNO ) VALUES ( 
    72. 7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
    73. , 3000, NULL, 20); 
    74. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    75. DEPTNO ) VALUES ( 
    76. 7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
    77. , 5000, NULL, 10); 
    78. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    79. DEPTNO ) VALUES ( 
    80. 7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
    81. , 1500, 0, 30); 
    82. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    83. DEPTNO ) VALUES ( 
    84. 7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
    85. , 1100, NULL, 20); 
    86. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    87. DEPTNO ) VALUES ( 
    88. 7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
    89. , 950, NULL, 30); 
    90. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    91. DEPTNO ) VALUES ( 
    92. 7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
    93. , 3000, NULL, 20); 
    94. INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    95. DEPTNO ) VALUES ( 
    96. 7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
    97. , 1300, NULL, 10); 
    98. commit;
    99.  
    100. INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    101. 1, 700, 1200); 
    102. INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    103. 2, 1201, 1400); 
    104. INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    105. 3, 1401, 2000); 
    106. INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    107. 4, 2001, 3000); 
    108. INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    109. 5, 3001, 9999); 
    110. commit;
    111.

    现在我们可以看到有3张表，分别是：emp(员工信息表）、dept(部门信息表）、salgrade(薪水等级表），三张表的各个构成如下：

    emp(员工信息表）

     dept(部门信息表）

    salgrade(薪水等级表）

    下面开始我们的练习题：

    第一题：取得每个部门最高薪水的人员名称

    思路：

    SELECT DEPTNO,max(sal)as avgsal FROM `emp`GROUP BY DEPTNO;

    先找出每个部门的最高薪水，然后作为临时表与emp连接查询该薪水对应的员工。

    答案：

    1. SELECT 
    2. 	emp.ENAME,t.* 
    3. FROM 
    4. 	emp 
    5. JOIN 
    6. 	(SELECT DEPTNO,max(sal)as maxsal FROM `emp`GROUP BY DEPTNO)t 
    7. ON 
    8. 	t.DEPTNO = emp.DEPTNO 
    9. and 
    10. 	t.maxsal = emp.SAL

     第二题：哪些人薪水在部门的平均薪水之上

    思路：

    SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO

    同样先找出每个部门的平均薪资，然后再作为临时表和emp连接查询大于此工资的人

    答案：

    1. SELECT 
    2. 	emp.ENAME,emp.SAL,t.* 
    3. FROM 
    4. 	emp 
    5. JOIN 
    6. 	(SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO)t 
    7. on 
    8. 	t.DEPTNO = emp.DEPTNO 
    9. and 
    10. 	emp.SAL>t.avgsal

    第三题：取得部门中（所有人的）平均薪水的等级 / 平均的薪水等级

    （1）平均薪水的等级

    思路：先找出平均薪水再去连接薪水等级表得到等级

    答案：

    1. SELECT 
    2. 	salgrade.GRADE,t.* 
    3. FROM 
    4. 	salgrade 
    5. JOIN 
    6. 	(SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO)t 
    7. on
    8. 	t.avgsal BETWEEN salgrade.LOSAL and salgrade.HISAL

    （2）平均的薪水等级

    思路：先连接薪水等级的表，然后再算等级的平均值

    答案：

    1. SELECT 
    2. 	emp.DEPTNO,avg(salgrade.GRADE)as avgGRADE
    3. FROM 
    4. 	emp 
    5. JOIN 
    6. 	salgrade 
    7. on 
    8. 	emp.SAL BETWEEN salgrade.LOSAL and salgrade.HISAL 
    9. GROUP BY emp.DEPTNO

    第四题：不用组函数（Max），取得最高薪水

    思路：通过排序+limit 1 来获取最值

    答案： 

    1. SELECT 
    2. 	emp.ENAME,emp.SAL 
    3. FROM 
    4. 	emp 
    5. ORDER BY emp.SAL DESC LIMIT 1

    第五题：取得平均薪水最高的部门的部门名称

    思路：先计算各部门的平均薪水得到临时表，然后和部门信息表连接获取名称，并用max获取最值

    答案：

    第一种：

    1. SELECT 
    2. 	dept.DNAME, max(t.avgsal)as maxavgsal 
    3. FROM 
    4. 	dept 
    5. JOIN 
    6. 	(SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO)t 
    7. on dept.DEPTNO = t.DEPTNO

     第二种：

    1. SELECT 
    2. 	dept.DNAME, max(t.avgsal)as maxavgsal 
    3. FROM 
    4. 	dept 
    5. JOIN
    6. 	(SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO ORDER BY avgsal DESC LIMIT 1)t 
    7. on dept.DEPTNO = t.DEPTNO

     【重要】第六题：求平均薪水等级最低的部门的部门名称

    思路：先求平均薪水最低的部门，然后用薪水连接薪水等级表对应等级，用部门编号连接部门信息表对应部门名称。

    1. 求出最低平均值
    2. SELECT avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO ORDER BY avgsal ASC LIMIT 1
    3.  
    4. 求出最低平均值对应等级
    5. SELECT salgrade.GRADE FROM salgrade JOIN (SELECT avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO ORDER BY avgsal ASC LIMIT 1)r on r.avgsal BETWEEN salgrade.LOSAL and salgrade.HISAL
    6.  
    7. 求出dept和salgrade联合
    8. select dept.DNAME,avg(sal)as avgsal FROM emp JOIN dept on emp.DEPTNO = dept.DEPTNO GROUP BY emp.DEPTNO

    答案：

    1. SELECT 
    2. 	t.*,salgrade.GRADE 
    3. FROM 
    4. 	(select dept.DNAME,avg(sal)as avgsal FROM emp JOIN dept on emp.DEPTNO = dept.DEPTNO GROUP BY emp.DEPTNO)t join 
    5. 	salgrade 
    6. on 
    7. 	t.avgsal BETWEEN salgrade.LOSAL and salgrade.HISAL 
    8. WHERE 
    9. 	GRADE = (SELECT 
    10. 		salgrade.GRADE 
    11. 	FROM 
    12. 		salgrade 
    13. 	JOIN 
    14. 		(SELECT avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO ORDER BY avgsal ASC LIMIT 1)r 
    15. 	on r.avgsal BETWEEN salgrade.LOSAL and salgrade.HISAL)

     第七题：取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名

    思路：

    1、先找出领导人编号MGR

    2、然后找出非领导人（普通员工）的最高薪水maxsal

    3、然后再整个emp中查找薪水>maxsal且编号属于领导的员工姓名

    答案： 

    1. SELECT 
    2. 	ENAME 
    3. FROM 
    4. 	emp WHERE SAL>(
    5. 	SELECT 
    6. 		max(SAL)as maxsal 
    7. 	FROM 
    8. 		emp 
    9. 	WHERE EMPNO NOT in(
    10. 		SELECT DISTINCT 
    11. 			MGR 
    12. 		FROM 
    13. 			emp 
    14. 		WHERE 
    15. 			MGR is NOT null)) 
    16. 		and 
    17. 			EMPNO 
    18. 		in (
    19. 			SELECT DISTINCT 
    20. 				MGR 
    21. 			FROM 
    22. 				emp 
    23. 			WHERE MGR is NOT null)

    第八题：取得薪水最高的前五名

    SELECT ENAME,SAL FROM emp ORDER BY SAL DESC LIMIT 5

    第九题：取得薪水最高的6-10名

    SELECT ENAME,SAL FROM emp ORDER BY SAL DESC LIMIT 5,5

    第十题：取得最后入职的5名员工

    SELECT ENAME,HIREDATE FROM emp ORDER BY HIREDATE DESC LIMIT 5

    第十一题：取得每个薪水等级有多少员工

    思路：先找出每个薪水对应的等级，然后按照等级分组，并进行count（*）

    答案：

    1. SELECT 
    2. 	salgrade.GRADE,count(*)
    3. FROM 
    4. 	emp 
    5. JOIN 
    6. 	salgrade 
    7. WHERE 
    8. 	emp.SAL BETWEEN salgrade.LOSAL AND salgrade.HISAL 
    9. GROUP BY 
    10. 	salgrade.GRADE

    【重要】第十二题：列出所有员工及对应领导的姓名

    思路：通过emp表的自连接，a的MGR = b的员工编号得出其领导

    答案：

    1. select 
    2. 	a.ENAME '员工',b.ENAME '领导' 
    3. from 
    4. 	emp a
    5. left JOIN
    6. 	emp b
    7. on 
    8. 	a.mgr = b.EMPNO

     第十三题：列出受雇日期早于其上级领导的所有员工的编号、姓名、部门名称

    思路：在上一题的基础上增加判断条件（a.HIREDATE

    答案：

    1. select 
    2. 	a.EMPNO '编号',a.ENAME '姓名',a.HIREDATE '雇佣时间',dept.DNAME '部门名称',b.ENAME '领导' ,b.HIREDATE
    3. from 
    4. 	emp a
    5. left JOIN
    6. 	emp b
    7. on 
    8. 	a.mgr = b.EMPNO
    9. JOIN
    10. 	dept
    11. on 
    12. 	a.DEPTNO = dept.DEPTNO
    13. where 
    14. 	a.HIREDATE<b.HIREDATE

    第十四题：列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

    SELECT emp.*,dept.* FROM emp JOIN dept on emp.DEPTNO = dept.DEPTNO

    第十五题：列出至少有5个员工的所有部门

    SELECT DEPTNO FROM emp GROUP BY DEPTNO HAVING count(*) >= 5

    第十六题：列出薪水比“SMITH”多的所有员工的信息

    答案：

    select emp.* FROM emp where sal>(SELECT SAL FROM emp where ENAME = 'SMITH')

    第十七题：列出所有“CLERK”（办事员）的姓名及其部门名称、部门的人数

    思路：

    1、先按照部门分组得出每个部门有多少人

    2、与上面临时表连接，并判断JOB = 'CLERK'的员工在那些部门得出所在部门人数

    3、与dept连接得到部门的名称

    答案：

    1. SELECT 
    2. 	emp.ENAME,emp.DEPTNO,dept.DNAME ,t.num as '部门人数'
    3. FROM 
    4. 	emp 
    5. JOIN 
    6. 	dept 
    7. on 
    8. 	emp.DEPTNO = dept.DEPTNO
    9. left JOIN
    10. 	(SELECT count(*)as num,emp.DEPTNO from emp GROUP BY DEPTNO)t
    11. ON
    12. 	emp.DEPTNO = t.DEPTNO
    13. WHERE 
    14. 	JOB = 'CLERK'

    第十八题：列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

    思路：

    1、按照工作分组，求出各个工作的最低薪水

    2、然后用emp与上面临时表相连，求出薪水大于最低薪水的人的信息，并进行count

    或者直接按工作分组，求出最低薪水对应的工作，然后直接计算count，将条件放到having中。

    答案： 

    1. select 
    2. 	count(*)as num,JOB 
    3. FROM 
    4. 	emp 
    5. GROUP BY 
    6. 	job 
    7. HAVING JOB in (SELECT JOB from emp GROUP BY JOB HAVING min(SAL)>1500)

    第十九题：列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部的部门编号

    答案：

    1. SELECT 
    2. 	ENAME 
    3. FROM 
    4. 	emp 
    5. WHERE DEPTNO = (SELECT DEPTNO FROM dept WHERE DNAME = "SALES")

     【重要】第二十题：列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资的等级

    思路：

    1、先求出平均薪水

    2、然后就是，工资等级需要连接薪水等级表、所在部门需要连接dept

    答案：

    1. SELECT
    2. 	emp.ENAME,dept.DNAME,l.ENAME,salgrade.GRADE
    3. from
    4. 	emp
    5. JOIN
    6. 	dept
    7. ON
    8. 	dept.DEPTNO =emp.DEPTNO
    9. LEFt JOIN
    10.   emp l
    11. ON
    12. 	emp.MGR = l.EMPNO
    13. JOIN
    14. 	salgrade
    15. ON
    16. 	emp.SAL BETWEEN salgrade.LOSAL AND salgrade.HISAL
    17. WHERE
    18. 	emp.SAL>(SELECT avg(SAL) from emp)

     第二十一题：列出与“SCOTT”从事相同工作的所有员工及部门名称

    思路：找出SCOTT从事的工作。然后连接dept表接口

    答案：

    1. SELECT 
    2. 	ENAME,JOB,dept.DNAME 
    3. FROM 
    4. 	emp 
    5. JOIN 
    6. 	dept 
    7. ON 
    8. 	emp.DEPTNO = dept.DEPTNO 
    9. WHERE 
    10. 	JOB = (SELECT JOB FROM emp WHERE ENAME = "SCOTT")

    第二十二题：列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

    答案：

    SELECT ENAME,SAL FROM emp WHERE SAL in (SELECT DISTINCT SAL FROM emp WHERE DEPTNO = 30) AND DEPTNO < 30

    第二十三题：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金，部门名称

    思路：

    1、先找出部门30中的最高薪水

    2、连接dept获取部门名称即可

    答案：

    1. SELECT 
    2. 	ENAME,SAL,dept.DNAME 
    3. FROM 
    4. 	emp 
    5. JOIN 
    6. 	dept 
    7. on 
    8. 	dept.DEPTNO = emp.DEPTNO 
    9. WHERE 
    10. 	SAL > (SELECT max(SAL) FROM emp WHERE DEPTNO = 30)

    第二十四题：列出在每个部门工作的员工数量，平均工资和平均服务期限

    思路：按照部门分组，注意日期的处理函数

    答案： 

    1. SELECT 
    2. 	dept.DEPTNO,count(ENAME),IFNULL(avg(SAL),0) ,avg(TIMESTAMPDIFF(YEAR,HIREDATE,NOW()))
    3. FROM 
    4. 	emp 
    5. right JOIN 
    6. 	dept 
    7. on 
    8. 	emp.DEPTNO = dept.DEPTNO 
    9. GROUP BY 
    10. 	dept.DEPTNO

    【注意】MySQL中对应日期计算的函数

    1. Mysql中对于日期的计算的函数 间隔类型可以是 
    2. YEAR 年
    3. QUARTER 季度
    4. MONTH 月
    5. WEEK 周
    6. DAY 日
    7. HOUR 小时
    8. MINUTE 分钟
    9. SECOND 秒
    10.  
    11. SELECT TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) FROM emp

    第二十五题：列出所有员工的姓名、部门名称、工资

    思路：

    答案：

    1. SELECT 
    2.     e.ENAME,d.DNAME,e.SAL
    3. FROM 
    4.     emp e
    5. LEFT JOIN 
    6.     dept d
    7. on 
    8.     e.DEPTNO = d.DEPTNO

     第二十六题：列出所有部门的详情信息和人数

    答案：

    SELECT dept.* ,count(ENAME) FROM emp right JOIN dept on emp.DEPTNO = dept.DEPTNO GROUP BY DEPTNO

     第二十七题：列出各种工作的最低工资及从事此工作的雇员姓名

    思路：

    1、按照工作分组，得出各工作的最低薪水及工作

    2、emp和临时表连接，判断条件为工作相同，薪水相同，从而得出雇员姓名

    答案：

    1. SELECT 
    2. 	emp.ENAME,t.* 
    3. FROM 
    4. 	emp 
    5. JOIN 
    6. 	(SELECT MIN(sal)as MINSAL,JOB from emp GROUP BY JOB)t 
    7. on 
    8. 	emp.JOB = t.JOB AND emp.SAL = t.MINSAL

     第二十八题：列出各个部门的MANAGER（领导）的最低薪金

    思路：

    1、与dept连接找出各个部门的MGR（因为有些部门可能没有人员，所以连接dept更精确）

    2、按照部门分组，且工作等于MANAGER的人，求min（sal）

    答案：

    1. SELECT 
    2. 	min(SAL) ,t.MGR,t.DEPTNO 
    3. FROM 
    4. 	emp 
    5. right JOIN 
    6. 	(SELECT MGR,emp.DEPTNO FROM emp RIGHT JOIN dept ON emp.DEPTNO = dept.DEPTNO )t 
    7. on 
    8. 	t.DEPTNO = emp.DEPTNO  
    9. WHERE 
    10. 	JOB = "MANAGER" 
    11. GROUP BY 
    12. 	t.DEPTNO

     第二十九题：列出所有员工的年工资，按年薪从低到高排序

    SELECT ENAME,(SAL+IFNULL(COMM,0))*12 as YEARSsal FROM emp ORDER BY YEARSsal ASC

    第三十题：求出员工领导薪水超过3000的员工名称与领导名称

    思路：直接自连接，领导薪水>3000

    答案：

    1. //第一种
    2. SELECT 
    3. 	emp.ENAME '员工名称', t.ENAME '领导名称' 
    4. FROM 
    5. 	emp 
    6. JOIN 
    7. 	(SELECT a.EMPNO,a.ENAME FROM emp a left JOIN emp b on a.MGR = b.EMPNO WHERE a.SAL>3000)t 
    8. ON 
    9. 	t.EMPNO = emp.MGR
    10.  
    11. //第二种（自连接写法）
    12. SELECT 
    13. 	a.ENAME'员工名称',b.ENAME'领导名称' 
    14. FROM 
    15. 	emp a 
    16. JOIN 
    17. 	emp b 
    18. ON 
    19. 	a.MGR = b.EMPNO 
    20. WHERE b.SAL > 3000

    第三十一题：求出部门名称中，带'S'字符的部门员工的工资合计、部门人数

    思路：按部门分组，与dept连接，判断DNAME like "%S%"

    答案：

    1. SELECT 
    2. 	dept.*,count(emp.ENAME),ifnull(sum(emp.SAL),0)
    3. FROM 
    4. 	emp 
    5. right join 
    6. 	dept 
    7. ON 
    8. 	emp.DEPTNO = dept.DEPTNO 
    9. WHERE
    10. 	dept.DNAME LIKE "%S%"
    11. GROUP BY 
    12. 	dept.DEPTNO

    第三十二题： 给任职日期超过30年的员工加薪10%

    UPDATE emp SET SAL = SAL*1.1 WHERE (TIMESTAMPDIFF(YEAR,HIREDATE,NOW()))>30

    力扣SQL题解

    178.分数排名

     

    编写 SQL 查询对分数进行排序。排名按以下规则计算:

    • 分数应按从高到低排列。
    • 如果两个分数相等，那么两个分数的排名应该相同。
    • 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

    按 score 降序返回结果表。

    查询结果格式如下所示。

     结题思路：首先创建这张表

    1. Create table If Not Exists Scores (id int, score DECIMAL(3,2))
    2.  
    3. insert into Scores (id, score) values ('1', '3.5')
    4. insert into Scores (id, score) values ('2', '3.65')
    5. insert into Scores (id, score) values ('3', '4.0')
    6. insert into Scores (id, score) values ('4', '3.85')
    7. insert into Scores (id, score) values ('5', '4.0')
    8. insert into Scores (id, score) values ('6', '3.65')

    这个题这样梳理一下可能更容易理解：

    最后的结果包含两个部分，第一部分是降序排列的分数，第二部分是每个分数对应的排名。

    第一部分不难写：

    1. select a.Score as Score
    2. from Scores a
    3. order by a.Score DESC

    比较难的是第二部分，假设现在有一个分数X，如何算出它的排名Rank呢？我们可以先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。可以按一下理解

    select b.Score from Scores b where b.Score >= X;

    我们要的是集合H去重之后的元素个数，因此变为如下所示：

    select count(distinct b.Score) from Scores b where b.Score >= X as Rank;

    那此时的X在题目中是什么，就是对应到第一部分的分数，所以这里的C就是a.Score，将两个部分结合起来为：

    1. select a.Score as Score,
    2. (select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
    3. from Scores a
    4. order by a.Score DESC
    5.

    180.连续出现的数字

    编写一个 SQL 查询，查找所有至少连续出现三次的数字。

    返回的结果表中的数据可以按 任意顺序 排列。

    查询结果格式如下面的例子所示：

    解题思路：

    首先这道题看起来很简单，但是比较复杂的一个点是连续出现三次，所以我们需要处理这个问题，我们通过多表连接即可实现。代码如下：

    1. SELECT DISTINCT
    2.     l1.Num AS ConsecutiveNums
    3. FROM
    4.     Logs l1,
    5.     Logs l2,
    6.     Logs l3
    7. WHERE
    8.     l1.Id = l2.Id - 1
    9.     AND l2.Id = l3.Id - 1
    10.     AND l1.Num = l2.Num
    11.     AND l2.Num = l3.Num
    12.

    182:查找重复的电子邮箱 

     这个题目很简单，我们可以用having子句，having子句和where的区别是having子句是用在group by后面的条件语句。

    答案：

    1. select Email
    2. from Person
    3. group by Email
    4. having count(Email) > 1;

    184:部门工资最高的员工

     

    首先，我们看到题目很简单的会直接连接两表分组求最值

    1. select Department.name as Department,Employee.name as Employee,max(Employee.salary) as salary
    2. from Employee left join Department on Employee.departmentId = Department.id
    3. group by Department

    得到的结果是：

    但是在此题中，我们要注意的是每个部门最高工资的不仅只有一个人。所有我们不能直接两表连接，分组求最值，这样得出的结果每个部门只会有一个人，不符合题意。我们应该先求出每个部门的最高工资是多少，然后再连接用IN得出有哪些人是该工资。

    1. SELECT
    2.     DepartmentId, MAX(Salary)
    3. FROM
    4.     Employee
    5. GROUP BY DepartmentId;
    1. SELECT
    2.     Department.name AS 'Department',
    3.     Employee.name AS 'Employee',
    4.     Salary
    5. FROM
    6.     Employee
    7.         JOIN
    8.     Department ON Employee.DepartmentId = Department.Id
    9. WHERE
    10.     (Employee.DepartmentId , Salary) IN
    11.     (   SELECT
    12.             DepartmentId, MAX(Salary)
    13.         FROM
    14.             Employee
    15.         GROUP BY DepartmentId
    16. 	)

     185：部门工资前三高的所有员工

    公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

    编写一个SQL查询，找出每个部门中 收入高的员工 。

     

    答案： 此题首先需要得出的是每个部门前三高的工资，然后再连接部门表，一般我们找的是每个部门的最高薪资或者最低薪资，可以通过GroupBy和MAX、MIN函数求出，但这种求出前几的情况不是很好处理，下面我们介绍几种窗口函数（很重要）

    常用窗口函数总结：

    1. row_number() over(partition by字段1 order by 字段2) 的结果是每一行记录生成一个序号，依次排序且排序的序号不会重复
    2. rank() over(partition by字段1 order by 字段2) 的结果会考虑排序字段值相同的情况，若排序字段的值相同则其序号是一样的,后续不同字段值的序号为（前一行序号+N,其中N为前一个字段值重复的行数），比如 1 1 3 4 4 4 7
    3. dense_rank() over(partition by字段1 order by 字段2) 的结果也会考虑排序字段值相同的情况，即排序字段的值相同那么他们的序号是一样的，但是与rank() 的区别是后续不同字段值的序号为（前一行序号+1），比如 1 1 2 2 3 4 5

    代码：

    1. SELECT 
    2.     d.Name  Department,
    3.     e.Name  Employee,
    4.     e.Salary
    5. FROM (
    6.     SELECT 
    7.         DENSE_RANK() OVER (partition by DepartmentId order by Salary desc) AS ranking, 
    8.         DepartmentId, Name, Salary
    9.     FROM employee
    10. ) e
    11. LEFT JOIN department d ON e.DepartmentId=d.id
    12. WHERE e.ranking <= 3;

     

  • 相关阅读:
    安全测试BurpSuite-抓包篡改数据
    Rational rose 2007 下载和安装教程
    【lambda表达式】常用的函数式接口
    Postgresql事物快照介绍
    灾难恢复架构规划要点
    使用idea，操作git创建分支,回滚版本，合并分支 ---全流程配图---
    java基于微信小程序的在线学习考试系统 uniapp 小程序
    C# 一些工具集优化使用（String，Random，Assembly，Json）
    离子交换树脂在除重金属领域的应用
    C/C++ 中typedef 关键字的使用 (给类型起别名)
  • 原文地址：https://blog.csdn.net/XZB119211/article/details/128072636