LeetCode-SQL练习

SQL语句

lc175

1.join

inner join: 只取两者都有的部分

left join: 取左表

right join:取右表

outer join 取左右表并集

lc175

select FirstName, LastName, City, State from Person LEFT JOIN Address on Person.PersonId=Address.PersonId;

2.排序

 先按 height 降序排序，再按 name 升序排序
 SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC;

limit & offset

1. select* from article LIMIT 1,3

2.select * from article LIMIT 3 OFFSET 1

上面两种写法都表示取2,3,4三条条数据
当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如
当 limit后面跟一个参数的时候，该参数表示要取的数据的数量
例如 select* from article LIMIT 3  表示直接取前三条数据，类似sqlserver里的top语法。
例如select * from article LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

lc176

取第二高的薪水:

注意这里distinct 去重

1.用临时表防范空值
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
假设我们有如图所示的数据，offset 1会超出范围 image
查询的结果为
{"headers": ["SecondHighestSalary"], "values": []}
而实际上预期结果为（以上可以达到）
{"headers": ["SecondHighestSalary"], "values": [[null]]}

2.用ifnull【在上面的外面又套了isnull】
ifnull(a,b)函数解释：
如果value1不是空，结果返回a
如果value1是空，结果返回b
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary;

作者：LeetCode
链接：https://leetcode-cn.com/problems/second-highest-salary/solution/di-er-gao-de-xin-shui-by-leetcode/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

lc177

第N高的薪水。

本来想取前N然后倒过来，但是如果<N的话就错了

      select salary  from (select distinct salary from Employee order by salary desc limit N) B order by salary limit 1

这里需要在return外面做运算。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
      SET N=N-1;
  RETURN (
      # Write your MySQL query statement below.

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET N),
    NULL) AS SecondHighestSalary

  );
END

lc178

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| 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    |
+-------+------+
重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/rank-scores
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

表取别名。

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'Rank'
from Scores a
order by a.Score DESC;
 因为这个版本不允许使用Rank做子表名，解决方法是添加单引号. rank是mysql中的关键字,变成'Rank'就能通过

作者：johnbear007
链接：https://leetcode-cn.com/problems/rank-scores/solution/fen-cheng-liang-ge-bu-fen-xie-hui-rong-yi-hen-duo-/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

函数

# Write your MySQL query statement below
select Score ,dense_rank() over( order by S.Score desc) as "Rank" from Scores S

作者：yi-qie-cong-jian
链接：https://leetcode-cn.com/problems/rank-scores/solution/dense_rank-han-shu-he-rankhan-shu-de-qu-267eh/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

值得注意的三个窗口函数。现在给定五个成绩：99，99，85，80，75。
DENSE_RANK()。如果使用 DENSE_RANK() 进行排名会得到：1，1，2，3，4。
RANK()。如果使用 RANK() 进行排名会得到：1，1，3，4，5。
ROW_NUMBER()。如果使用 ROW_NUMBER() 进行排名会得到：1，2，3，4，5。

作者：superkakayong
链接：https://leetcode-cn.com/problems/rank-scores/solution/zi-jie-ti-ku-178-zhong-deng-sqlfen-shu-pai-ming-1s/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

窗口函数：https://zhuanlan.zhihu.com/p/138282683

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。

#按班级分类，将成绩降序排序
SELECT*,
rank() over ( PARTITION BY 班级 ORDER BY 成绩 DESC)  AS ranking  
FROM class;

窗口函数这里的“窗口”表示范围，可以理解为将原数据划分范围，即分组，然后用函数实现某些目的
窗口函数有分组和排序的功能
不减少原表的行数

having关键字

from : https://www.cnblogs.com/chenziyu/p/9253931.html

sql中的having语句是在使用group by的时候使用的。

通常where语句是在group by之前做数据筛选的，而having语句是对group by之后的结果进行筛选的。

例如：

从商品销售表里取得用户A购买的各种商品的总数：

SELECT
    SUM(TOTAL)
FROM
    COST
WHERE
    USERID = 'A'
GROUP BY 
    PRODUCTID
 

从商品销售表里取得用户A购买的各种商品的总数，并且改总数大于10：

SELECT
    SUM(TOTAL)
FROM
    COST
WHERE
    USERID = 'A'
GROUP BY
    PRODUCTID
HAVING
    SUM(TOTAL) > 10

lc 181

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/employees-earning-more-than-their-managers
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/solution/chao-guo-jing-li-shou-ru-de-yuan-gong-by-leetcode/

1.
select a.name as Employee from Employee as a ,Employee as b where a.Salary >b.Salary and a.managerId = b.Id;
2.join
SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;

作者：LeetCode
链接：https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/solution/chao-guo-jing-li-shou-ru-de-yuan-gong-by-leetcode/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

lc 182

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：

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

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/duplicate-emails
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

用了group by & having

1.me
select distinct a.Email from Person as a,Person as b where a.Email=b.Email and a.Id<>b.Id;
2.
select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1
;
3.
select Email
from Person
group by Email
having count(Email) > 1;
作者：LeetCode
链接：https://leetcode-cn.com/problems/duplicate-emails/solution/cha-zhao-zhong-fu-de-dian-zi-you-xiang-by-leetcode/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

lx 183

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

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

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
例如给定上述表格，你的查询应返回：

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

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/customers-who-never-order
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

select name as Customers from Customers where id not in (select CustomerId as id from Orders);

184

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+
解释：

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

这个题用left join不对，join对了。原因：join 即inner join，员工有公司无就不返回了。但是left join，员工有公司无还会返回无公司的员工。

where 后面的语句没有想到。

# Write your MySQL query statement below
select Department.name as Department, Employee.name as Employee ,Salary from Employee join Department
on Department.id=Employee.DepartmentId WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;


select d.name as Department,a.name as Employee, a.salary as Salary from Employee a,Department d,(select max(Salary) as ms,DepartmentId  from Employee group by DepartmentId ) c where a.DepartmentId=d.id and d.id=c.DepartmentId and a.Salary=c.ms;

遇到的题

一条SQL 语句查询出每门课都大于80 分的学生姓名。
table：grades
name course score
张三语文 81
张三数学 75
李四语文 76
李四数学 90
王五语文 81
王五数学 100

需要反向思维

select distinct name from grades where name not in (select distinct name from table where score<=80)

最后编辑于：2021.09.16 23:38:53

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 213,254评论 6赞 492
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,875评论 3赞 387
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 158,682评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,896评论 1赞 285
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,015评论 6赞 385
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,152评论 1赞 291
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,208评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,962评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,388评论 1赞 304
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,700评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,867评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,551评论 4赞 335
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,186评论 3赞 317
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,901评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,142评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,689评论 2赞 362
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,757评论 2赞 351

LeetCode-SQL练习

SQL语句

lc175

lc176

lc177

lc178

having关键字

lc 181

lc 182

lx 183

184

遇到的题

推荐阅读更多精彩内容