1.LeetCode 175
主要学习Left Join的用法
SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
题目大意
表Person:
| Column Name | Type |
|---|---|
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
PersonId is the primary key column for this table.
表: Address
| Column Name | Type |
|---|---|
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
AddressId is the primary key column for this table.
将表Person和Address合并，并返回FirstName, LastName, City, State，如果某一个人不存在city，State的信息，在相对应的位置上也要标为NULL值。
这里就需要用到left join来解决，因为一定保证的是Person的信息都必须存在，Address上对应不存在的信息可以赋上NULL值，left join的使用往往需要加上条件限制ON
SELECT FirstName,LastName,City,State
FROM person s1 LEFT JOIN address s2 ON s1.PersonId = s2.PersonId
2.LeetCode 176
主要学习取最大值和limit的用法
| Id | Salary |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
最后希望得到的是第二大的工资，如果不存在返回NULL
这上方的样例应该是返回
| SecondHighestSalary |
|---|
| 200 |
标准的解答如下所示：
SELECT max(Salary) as SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT max(Salary) FROM Employee)
先获取到所有小于最大值的值，再获取其中的最大值，这样即使是不存在也会返回NULL。
但是如果题目不要求一定要返回NULL，可以如下写：
SELECT Salary as SecondHighestSalary
from Employee
order by Salary ASC
limit 1,1
从小到大排序，然后获取第二行的数据，这里limit 1,1就表示从下标为1的行开始取1行数据
2.LeetCode 181
主要学习表的合并方法
对于表Employee：
| Id | Name | Salary | ManagerId |
|---|---|---|---|
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
根据ManagerId可以看出3号Sam是Joe的管理者，4号Max是Henry的管理者，这里找到所有比自己管理者工资高的员工的名字
所以这个例子应该返回
| Employee |
|---|
| Joe |
简单的理解就是，将员工的信息和对应的管理人员的信息合并到同行来比较工资大小，下面两种简单的写法
select s1.name
from Employee as s1 join Employee as s2 #将两个同样的表合并，但保证是员工和其管理者对应到同一行
where s1.ManagerId = s2.Id and s1.salary>s2.salary
select s1.name as Employee
from Employee as s1 join Employee as s2 on (s1.ManagerId = s2.Id) #将两个同样的表合并，但保证是员工和其管理者对应到同一行
where s1.salary>s2.salary
2.LeetCode 182
主要学习having， group by , in的用法
Having语句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。合计函数比如SUM，COUNT，AVERAGE等
GROUP BY 语句
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
IN 操作符
IN 操作符允许我们在 WHERE 子句中规定多个值。
SQL IN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
对于表Person：
| Id | |
|---|---|
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
找到所有重复了的Email
select email from Person group by email having count(*) > 1
如果进行简单扩展找到所有出现了重复邮箱的行的信息，那么对于上面这个sample来说就是输出
| 1 | a@b.com |
| 3 | a@b.com |
那么只要在这句代码基础上进行in操作的添加
select * from Person
where email in
(select email from Person group by email having count(*) > 1)
2.LeetCode 183
主要学习表基础合并的用法
存在表customers和orders：
| Id | Name |
|---|---|
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
| Id | CustomerId |
|---|---|
| 1 | 3 |
| 2 | 1 |
找到不是任何人customer的用户名字，这里根据orders表可以看出1号用户有顾客3(Sam) , 2号用户有顾客4(Max)
也就是说只有Henry和Max不是任何人的客户，所以输出二者的名字
| Customers |
|---|
| Henry |
| Max |
select customers.`Name` as Customers
from
customers left join orders on (customers.Id = orders.CustomerId)
where
orders.Id is null
SELECT A.Name from Customers A
WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId)
SELECT A.Name from Customers A
LEFT JOIN Orders B on a.Id = B.CustomerId
WHERE b.CustomerId is NULL
SELECT A.Name from Customers A
WHERE A.Id NOT IN (SELECT B.CustomerId from Orders B)
2.LeetCode 183
主要学习sql函数的用法
对于Employee表：
| Id | Salary |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
函数传入一个N，找到不重复的工资排名第N位的工资
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT salary as getNthHighestSalary #distinct保证工资是不重复的
from employee
ORDER BY salary DESC
limit M,1
);
END;
SELECT getNthHighestSalary(2); #这句话不加在提交代码中，用来自己测试运行
1.LeetCode 178
主要学习select语句嵌套查询的用法
| Id | Score |
|---|---|
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
将上述表Scores根据成绩排名由大到小得到如下的结果
| Score | Rank |
|---|---|
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
SELECT
Score , (SELECT count(DISTINCT Score) FROM scores where Score>=s.score) Rank
from
scores s
ORDER BY Rank ASC
1.LeetCode 180
sql语句基本练习
| Id | Num |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
在上表Logs找到连续三个数字都相同的数字，那么在这个样例中就是1，可得
| ConsecutiveNums |
|---|
| 1 |
1.#将三个表做连接，来定义规则保证三者是连续相等的数字
SELECT
DISTINCT l1.Num as ConsecutiveNums
FROM
Logs l1 , Logs l2 , Logs l3
WHERE
l1.Num = l2.Num AND l1.Num = l3.Num
AND l1.Id = l2.Id-1
AND l2.Id = l3.Id-1
2.
SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary
from
Employee E,
Department D
WHERE E.DepartmentId = D.id
AND (DepartmentId,Salary) in
(SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId)
1.LeetCode 178
主要学习date类型的用法
| 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 |
在这个weather表中找到比前一天温度高的日子的id，这里应该返回2，4，如下：
| Id |
|---|
| 2 |
| 4 |
select
B.Id
from
weather A , weather B
where
A.Temperature<B.Temperature and TO_DAYS(B.Date)-TO_DAYS(A.Date) = 1
1.LeetCode596
主要学习having下的函数比较作为限制条件的用法
对函数产生的值来设定条件,举例来说，我们可能只需要知道哪些店的营业额有超过 $1,500。在这个情况下，我们不能使用 WHERE 的指令。那要怎么办呢？很幸运地，SQL 有提供一个 HAVING 的指令，而我们就可以用这个指令来达到这个目标。 HAVING 子句通常是在一个 SQL 句子的最后。一个含有 HAVING 子句的 SQL 并不一定要包含 GROUP BY 子句。HAVING 的语法如下：
SELECT "栏位1", SUM("栏位2")
FROM "表格名"
GROUP BY "栏位1"
HAVING (函数条件);
| student | class |
|---|---|
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
对于上表courses 中找到所有有至少5个不同学生选择了的课程
结果是：
| class |
|---|
| Math |
#这里可能会存在同样的学生选了同样的课被多次列举，所以后面用到了distinct student
select
class
from
courses
group by class
having count(distinct student)>=5
1.LeetCode627
主要学习update语句以及数据格式转化后的异或操作，ASCII，CHAR格式转化的用法
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
将表salary中所有sex的m转化成f，f转化成m
update salary
set sex = CHAR(ASCII('m')^ASCII('f')^ASCII(sex))
网友评论