几道SQL题目

1.查出各部门员工工资最高的员工信息。

原始数据表：



结果SQL语句：
  两种解决方案
2-1 ：
  

SELECT 
	 dep.name as Department , emp.name as Employee , emp.salary as Salary 
FROM 
	tb_employee emp ,tb_employee emp1, tb_department dep
WHERE
	emp.salary <=emp1.salary 
	AND emp.departmentId = emp1.departmentId
	AND emp.departmentId = dep.id 
GROUP BY 
	dep.name,emp.name ,emp.salary
HAVING COUNT(*)<=1
ORDER BY 
	emp.departmentId , emp.salary desc;

2-2 :
 

select  
	dep.name as Department , emp.name as Employee , emp.salary as Salary 
from 
	tb_employee emp , tb_department dep 
where
	emp.departmentId = dep.id and (emp.departmentId , emp.salary) in  (select departmentId , max(salary) from tb_employee group by  departmentId) ;

执行结果：



2.查找出没有购物的客户

原始数据表：



结果SQL：

SELECT 
	id AS ID ,
	name AS Name 
FROM 
	tb_customers 
WHERE 
	id NOT IN 
	(
	SELECT 
		DISTINCT(customerId) 
	FROM tb_orders
	)

结果如下：



3.分数排名
原始数据表



查询SQL：

select 
	s2.Score ,
	(
	select 
		count(distinct(s1.Score)) 
	from 
		tb_score s1 
	where 
		s1.Score>=s2.Score
	) as Rank 
from 
	tb_score s2 
order by 
	s2.score DESC;

查询结果：