数据库经典面试问题-leetcode习题_select e.name from employee e, employee m where e.-CSDN博客

本文链接：https://blog.csdn.net/qq_36327346/article/details/78205239

  ps：百度题目，可以看到完整描述 

  175. 
 Combine Two Tables 

 
 select 
   
 p.FirstName, p.LastName, a.City, a.State  
 from 
   
 Person p  
 left 
   
 outer 
   
 join 
   
 Address a  
 on 
   
 p.PersonId=a.PersonId;

 
 176. 
 Second Highest Salary 

 
 select 
   
 IFNULL( ( 
 select 
   
 e.Salary  
 from 
   
 Employee e  
 group 
   
 by 
   
 e.Salary  
 order 
   
 by 
   
 e.Salary  
 desc 
   
 limit 1, 1),  
 NULL 
 ) SecondHighestSalary; 

 
 177. 
 Nth Highest Salary 

 
 CREATE 
   
 FUNCTION 
   
 getNthHighestSalary(N  
 INT 
 )  
 RETURNS 
   
 INT 

 
 BEGIN 

 
 RETURN 
   
 (

 
          
 select 
   
 IFNULL(Salary,  
 NULL 
 ) Salary  
 from 
   
 ( 

 
 select 
   
 @row_num := @row_num+1 Rank, Salary  
 from 
   
 (

 
 select 
   
 Salary  
 from 
   
 Employee  
 group 
   
 by 
   
 Salary  
 desc

 
 ) t1  
 join 
   
 (

 
 select 
   
 @row_num := 0  
 from 
   
 dual

 
              
 ) t2 

 
 ) t  
 where 
   
 t.Rank=N

);

END

 
 180. 
 Consecutive Numbers 

 
 select 
   
 DISTINCT 
 (l1.Num)  
 from 
   
 Logs l1, Logs l2, Logs l3  
 where 
   
 l1.Id+1=l2.Id  
 and 
   
 l1.Id+2=l3.Id  
 and 
   
 l1.Num=l2.Num  
 and 
   
 l1.Num=l3.Num; 

 
 181. 
 Employees Earning More Than Their Managers 

 
 select 
   
 e. 
 Name 
   
 from 
   
 Employee e, Employee m  
 where 
   
 e.ManagerId=m.Id  
 and 
   
 e.Salary>m.Salary; 

 
 182. 
 Duplicate Emails 

 
 select 
   
 distinct 
 (p.Email)  
 from 
   
 Person p, Person q  
 where 
   
 p.Id!=q.Id  
 and 
   
 p.Email=q.Email; 

 
 183. 
 Customers Who Never Order 

 
 select 
   
 c. 
 Name 
   
 Customers  
 from 
   
 Customers c  
 where 
   
 c.Id  
 not 
   
 in 
   
 ( 

 
 select 
   
 CustomerId  
 from 
   
 Orders

)

 
 184. 
 Department Highest Salary 

 
 SELECT 
  d.Name  
 AS 
  Department, e.Name  
 AS 
  Employee, e.Salary  
 FROM 
  Employee e, Department d 
 WHERE 
  e.DepartmentId  
 = 
  d.Id  
 AND 
  e.Salary  
 = 
  ( 
 SELECT 
   
 MAX 
 (Salary)  
 FROM 
  Employee e2  
 WHERE 
  e2.DepartmentId  
 = 
  d.Id); 

 
 185. 
 Department Top Three Salaries 

 
 SELECT 
  d.Name  
 AS 
  Department, e.Name  
 AS 
  Employee, e.Salary  
 FROM 
  Employee e 
 JOIN 
  Department d  
 on 
  e.DepartmentId  
 = 
  d.Id 
 WHERE 
  ( 
 SELECT 
   
 COUNT 
 ( 
 DISTINCT 
  Salary)  
 FROM 
  Employee  
 WHERE 
  Salary  
 > 
  e.Salary 
 AND 
  DepartmentId  
 = 
  d.Id)  
 < 
   
 3 
   
 ORDER 
   
 BY 
  d.Name, e.Salary  
 DESC 
 ; 

 
 查询某门课是最高成绩的记录 

 
 第一种：先使用group by和max得到最高分数和学科，然后再查询一下score表，找到学科和分数都相同的记录 

 
 select b.* from (select max(score) t,course from score group by course) a,score b where a.t=b.score and a.course=b.course 

 
 第二种：先得到相同学科的最高分数，再查询score表，找到最高分数的记录 

 
 select * from score a where score=(select max(score) from score where course=a.course) 