175. Combine Two Tables

Description

Person 表：

Column Name	Type
PersonId	int
FirstName	varchar
LastName	varchar

Address 表：

Column Name	Type
AddressId	int
PersonId	int
City	varchar
State	varchar

查找 FirstName, LastName, City, State 数据，而不管一个用户有没有填地址信息。

solution

SELECT    FirstName,    LastName,    City,    StateFROM    Person P    LEFT JOIN Address A    ON P.PersonId = A.PersonId;

181. Employees Earning More Than Their Managers ??

Description

Employee 表：

Id	Name	Salary	ManagerId
1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000	NULL
4	Max	90000	NULL

查找薪资大于其经理薪资的员工信息。

solution

SELECT    E1.NAME AS Employee FROM    Employee E1    INNER JOIN Employee E2    ON E1.ManagerId = E2.Id    AND E1.Salary > E2.Salary;

183. Customers Who Never Order

Description

Curstomers 表：

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Orders 表：

Id	CustomerId
1	3
2	1

查找没有订单的顾客信息：

Customers

Henry

Max

soulution:

(1)子查询

SELECT    Name AS Customers FROM    CustomersWHERE   Id NOT IN  (SELECT CustomerId FROM Orders)

（2）

SELECT    C.Name AS CustomersFROM    Customers C    LEFT JOIN Orders O    ON C.Id = O.CustomerIdWHERE    O.CustomerId IS NULL;

184. Department Highest Salary ？？？

Description

Employee 表：

Id	Name	Salary	DepartmentId
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1

Department 表：

Id	Name
1	IT
2	Sales

查找一个 Department 中收入最高者的信息：

Department	Employee	Salary
IT	Max	90000
Sales	Henry	80000

soulution:

SELECT    D.NAME Department,    E.NAME Employee,    E.SalaryFROM    Employee E,    Department D,    ( SELECT DepartmentId, MAX( Salary ) Salary FROM Employee GROUP BY DepartmentId ) MWHERE    E.DepartmentId = D.Id    AND E.DepartmentId = M.DepartmentId    AND E.Salary = M.Salary;

176. Second Highest Salary ？？

Description

Id	Salary
1	100
2	200
3	300

查找工资第二高的员工。

solution

SELECT    (SELECT DISTINCT            Salary        FROM            Employee        ORDER BY Salary DESC        LIMIT 1 OFFSET 1) AS SecondHighestSalary

177. Nth Highest Salary

Description

查找工资第 N 高的员工。

CREATE FUNCTION getNthHighestSalary ( N INT )         RETURNS INT         BEGIN            SET N = N - 1;             RETURN (                     SELECT (                        SELECT DISTINCT Salary                         FROM Employee                         ORDER BY Salary DESC                         LIMIT N, 1 )              );        END

178. Rank Scores ？？

Description：

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

将得分排序，并统计排名。

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

Solution：

（1）解法一：对于每一个分数，从表中找出有多少个大于或等于该分数的不重复分数，然后降序排列

select Score,(select count(distinct Score) from Scores s2 where s2.Score >= s1.Score) Rank from Scores  s1order by Score DESC;

（2）解法二：使用联结 join，条件是左表的分数小于等于右表的分数时，对右表的分数进行计数（即计算有几个不重复的分数大于自己，计算结果就是rank），然后根据id分组后，再根据分数降序排列

select s1.Score, count(distinct s2.Score) Rankfrom Scores as s1 inner join Scores as s2 on s1.Score <= s2.Scoregroup by s1.Id order by s1.Score DESC;

180. Consecutive Numbers

Description

数字表：

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

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

ConsecutiveNums

1

solution:

(1)用l1分别和l2, l3内交，l1和l2的Id下一个位置比，l1和l3的下两个位置比，然后将Num都相同的数字返回即可

select distinct l1.Num ConsecutiveNums from Logs l1join Logs l2 on l1.Id = l2.Id - 1join Logs l3 on l1.Id = l3.Id - 2where l1.Num = l2.Num and l2.Num = l3.Num;

(2)直接在三个表的实例中查找，然后把四个条件限定上，就可以返回正确结果了 ??

SELECT DISTINCT l1.Num FROM Logs l1, Logs l2, Logs l3WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1AND l1.Num = l2.Num AND l2.Num = l3.Num;

626. Exchange Seats ???

seat 表存储着座位对应的学生。

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

要求交换相邻座位的两个学生，如果最后一个座位是奇数，那么不交换这个座位上的学生。

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

solution

对照上表及其查询结果可以得知，当原id为奇数时，交换座位后的id变为id+1,当原id为偶数时，交换座位后的id变为id-1,另一个方面需要考虑的是，学生人数为奇数时，最后一个学生的id不变，故应当用子查询确定学生的人数，然后分情况讨论即可

select (case      when mod(id,2)!=0 and id!=counts then id+1      when mod(id,2)!=0 and id=counts then id      else id-1 end)as id,student      from seat,(select count(*)as counts from seat)as seat_counts                order by id;