SQL 面试中有出现次数较多的查询语句解答
1.获取所有员工中工资第二高的薪金:
(
Employee
表)
解法
1:
SELECT MAX(Salary)
FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee );
解法 2:
select MAX(Salary) from Employee
WHERE Salary <> (select MAX(Salary) from Employee )
2.
获取偶数行数据:同上表
解法:
SELECT *
FROM(SELECT rownum, ID FROM Employee)
WHERE MOD(rownum,2)=0
3.找到每个部门的最高新:
(Employee 表)
(Department 表)
解法 1:
SELECT d.DeptName, MAX(e.Salary)
FROM Department d LEFT OUTER JOIN Employee e
ON e.ID = d.ID
GROUP BY DeptName
解法 2:
SELECT DeptID,MAX(Salary)
FROM Employee
GROUP BY DeptID
4.获取员工里有相同姓名和邮箱地址的人:
(Employee 表)