Leetcode之Database篇

SELECT a.NAME FROM Employee a, Employee b 
WHERE a.ManagerId = b.Id AND a.Salary > b.Salary;

2：Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

SELECT Email FROM Person GROUP BY Email HAVING COUNT(*)>1

具体关于group by 和having 的用法参考了别人的一篇博客

http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

3：Combine Two Tables

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

SELECT p.FirstName, p.LastName, a.City, a.State FROM Person p LEFT JOIN Address a USING (PersonId)

主要是两个表的连接，参考链接如下：

http://www.bkjia.com/Mysql/777046.html

http://www.cnblogs.com/devilmsg/archive/2009/03/24/1420543.html

4：Customers Who Never Order

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

SELECT name FROM Customers c LEFT JOIN Orders o on c.Id = o.CustomerId WHERE o.Id IS NULL

网址http://www.tuicool.com/articles/miAfii给出了三种方法，可供参考

5:Rising Temperature

Given a Weather table, write a SQL query to find all dates‘ Ids with higher temperature compared to its previous (yesterday‘s) dates.

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

 首先要内联，其次要注意查询的ID是哪一张表的ID，w1.Id.

其次有计算date天数的函数，课参考http://blog.chinaunix.net/uid-26921272-id-3385920.html

SELECT w1.Id FROM Weather w1 INNER JOIN Weather w2 
ON TO_DAYS(w1.Date) = TO_DAYS(w2.Date) + 1 AND w1.Temperature > w2.Temperature

6:Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

SELECT Max(Salary) FROM Employee
WHERE Salary < (SELECT Max(Salary) FROM Employee)

7:明天继续

Leetcode之Database篇

标签：