数据库操作

数据库操作

---

气温上升

给你一个叫Weather的表，列出气温比前一天高的行的id，例如：

• Given:

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

• return:

Id
2
4

SELECT a.id
FROM Weather as a, Weather as b
WHERE DATEDIFF(a.date, b.date) = 1 
      AND a.temperature > b.temperature

知识点：自联结，聚集函数DATEDIFF(date1, date2），当需要比较同列的值时，往往需要用到自联结（表自己和自己联结），为了消除二义性需要为表设置别名（AS）

Rising Temperature

---

找出薪水比经理高的雇员

例如：1（Joe）的经理是3（Sam）,且Joe的薪水比Sam高
表名为Employee

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

返回：

Employee
Joe

解1：

SELECT e1.Name as Employee
FROM Employee as e1, Employee as e2
WHERE e1.ManagerId = e2.Id AND e1.Salary > e2.Salary

解2：

SELECT e1.Name as Employee
FROM Employee as e1 INNER JOIN Employee as e2
ON e1.ManagerId = e2.Id AND e1.Salary > e2.Salary

知识点：别名，等值联结/内部联结。内部联结中，被联结两表中的行都能在另一个表里找到关联行。

Employees Earning More Than Their Managers

---

重复的Email

例如：
表名为Person

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

返回

Email
a@b.com

表Person中a@b.com出现了2次。

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

知识点：分组数据（GROUP BY… HAVING）,将表中各行按Email分组，并统计各组的行数COUNT(*)，组内的过滤需用HAVING而不是WHERE

Duplicate Emails

---

二表合并

表名Person

Column Name	Type
PersonId	int
FirstName	varchar
LastName	varchar

表名Address

Column Name	Type
AddressId	int
PersonId	int
City	varchar
State	varchar

将表Person和表Address合并显示为

FirstName	LastName	City	State

不论合并后，表中的人是否有地址

SELECT p.FirstName, p.LastName, a.City, a.State
FROM Person as p LEFT OUTER JOIN Address as a
ON p.PersonId = a.PersonId

知识点：外部联结(LEFT/RIGHT OUTER JOIN … ON)。注意表Person中出现的人不一定也在表Address中出现，需使用外部联结才能包含没有关联行的行，这里的LEFT表示结果包含左表（LEFT关键字左边的表Person）所有的行。

Combine Two Tables

---

没有订单的用户

Table: Customers.

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Table: Orders.

Id	CustomerId
1	3
2	1

由上面连个表返回

Customers
Henry
Max

表联结不写表名\表别名不行，且别名是区分大小写的，判断是否存在可以用

• WHERE field IS NULL

SELECT c.Name AS Customers
FROM Customers AS c 
LEFT JOIN Orders AS o 
ON c.Id = o.CustomerId
WHERE o.CustomerId IS NULL

• WHERE field NOT IN table

SELECT c.Name AS Customers
FROM Customers AS c
WHERE c.Id NOT IN (SELECT o.CustomerId 
                   FROM Orders AS o)

• WHERE NOT EXISTS

SELECT c.Name AS Customers
from Customers AS c
WHERE NOT EXISTS (SELECT 1 
                  FROM Orders AS o 
                  WHERE c.Id = o.CustomerId)

Customers Who Never Order