几道SQL题目

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

原始数据表：

[img]http://dl2.iteye.com/upload/attachment/0105/7427/8273b2fd-e169-3c1a-b711-1e5cbd39b9a2.png[/img]

结果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) ;

执行结果：

[img]http://dl2.iteye.com/upload/attachment/0105/7429/955d3bba-6792-3e91-a91e-2749caea7b8b.png[/img]

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

原始数据表：

[img]http://dl2.iteye.com/upload/attachment/0105/7431/93cbb13a-ef30-30bc-8d83-d6aab8878e69.png[/img]

结果SQL：

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

结果如下：

[img]http://dl2.iteye.com/upload/attachment/0105/7433/279e9886-8058-3a35-9e59-476a875529ec.png[/img]

3.分数排名
原始数据表

[img]http://dl2.iteye.com/upload/attachment/0105/7449/502d4b5b-d9bc-3652-93ac-64f055924650.png[/img]

查询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;

查询结果：

[img]http://dl2.iteye.com/upload/attachment/0105/7451/abe925ba-0074-33b8-bf1e-a9bcd0a41884.png[/img]