LeetCode做题记录（持续更新）

水深无声 2022-11-09 14:28 199阅读 0赞

1.查找单表中的重复数据

select  Email from Person group by Email having count(Email)>1;

总结：优先顺序where>group by>having>order by

where字句无法与聚合函数一起使用,如果要对分组查询的结果进行筛选，可以使用having子句

2.按条件修改数据（将表内某列特定值替换成其他值），只能使用update时

使用case、when

update salary set sex =

(case sex

when 'm' then 'f'

else 'm'

end

);

3.查找不在表里面的数据

涉及2张以上表的查询时，我们需要用到多表联结

select ...  
from 表1 as a  
left join 表2 as b  
on a.列名=b.列名  
where b.列名 is null;

4.组合两张表（从多表中查询数据），与3很像

select FirstName,LastName,City,State from Person left join Address on Person.PersonId=Address.PersonId;

\#注意：如果没有某个人的地址信息，使用 where 子句过滤记录将失败，因为它不会显示姓名信息，所以要用on

5.删除单张表中的重复数据

DELETE p1 FROM Person p1,  
    Person p2  
WHERE  
    p1.Email = p2.Email AND p1.Id > p2.Id

用自连接

**`DELETE p1`表示从p1表中删除满足`WHERE`条件的记录**

a. 从驱动表（左表）取出N条记录；  
b. 拿着这N条记录，依次到被驱动表（右表）查找满足WHERE条件的记录；

6.查询出单表中超过某个数据的数据

SELECT  
    a.Name AS 'Employee'  
FROM  
    Employee AS a,  
    Employee AS b  
WHERE  
    a.ManagerId = b.Id  
        AND a.Salary > b.Salary

自连接

从两个表里使用 Select 语句可能会导致产生 笛卡尔乘积 。在这种情况下，输出会产生 4\*4=16 个记录。然而我们只对雇员工资高于经理的人感兴趣。所以我们应该用 WHERE 语句加 2 个判断条件。

7.单表多个条件二选一查询

SELECT name, population, area FROM world WHERE area > 3000000 OR population > 25000000

或者

SELECT  
    name, population, area  
FROM  
    world  
WHERE  
    area > 3000000

UNION

SELECT  
    name, population, area  
FROM  
    world  
WHERE  
    population > 25000000

*方法二* 比 *方法一* 运行速度更快，而且使用 or 会使索引会失效，在数据量较大的时候查找效率较低，通常建议使用 union 代替 or。

8.找出所有影片描述为**非** `boring` (不无聊) 的并且** id 为奇数 **的影片，结果请按等级 `rating` 排列

我们可以使用 `mod(id,2)=1` 来确定奇数 id，然后添加 `description != 'boring'` 来解决问题。

select \*

from cinema

where description != 'boring' and mod(id,2) = 1

order by rating desc;

9.编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 **每个月 **的收入（revenue）列。

select id,

sum(case month when 'Jan' then revenue end) as  Jan\_Revenue,

sum(case month when 'Feb' then revenue end) as  Feb\_Revenue,

sum(case month when 'Mar' then revenue end) as  Mar\_Revenue,

sum(case month when 'Apr' then revenue end) as  Apr\_Revenue,

sum(case month when 'May' then revenue end) as  May\_Revenue,

sum(case month when 'Jun' then revenue end) as  Jun\_Revenue,

sum(case month when 'Jul' then revenue end) as  Jul\_Revenue,

sum(case month when 'Aug' then revenue end) as  Aug\_Revenue,

sum(case month when 'Sep' then revenue end) as  Sep\_Revenue,

sum(case month when 'Oct' then revenue end) as  Oct\_Revenue,

sum(case month when 'Nov' then revenue end) as  Nov\_Revenue,

sum(case month when 'Dec' then revenue end) as  Dec\_Revenue

from Department

group by id;

先使用group by按部门编号给表分成三类，再用sum计算每个月的总收入

10.编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 `id` 。

\#难点：如何获取前一天

select a.id as id

from Weather a,Weather b

where a.Temperature > b.Temperature and datediff(a.recordDate,b.recordDate) =1;

MySQL 使用 [DATEDIFF][] 来比较两个日期类型的值。

datediff(日期1, 日期2)：  
得到的结果是日期1与日期2相差的天数。  
如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。

另一个关于时间计算的函数是：  
timestampdiff(时间类型, 日期1, 日期2)  
这个函数和上面diffdate的正、负号规则刚好相反。  
日期1大于日期2，结果为负，日期1小于日期2，结果为正。

在“时间类型”的参数位置，通过添加“day”, “hour”, “second”等关键词，来规定计算天数差、小时数差、还是分钟数差。

11.有一个`courses` 表 ，有: **student (学生) **和 **class (课程)**。请列出所有超过或等于5名学生的课。

select class

from courses

group by class

having count(Distinct student)>=5;

先用group by把数据按照class分类，在用count统计每门课程一共有多少学生选，distinct用于学生在每个课中不被重复计算

12.编写一个 SQL 查询，获取 `Employee` 表中第二高的薪水（Salary）

题目类型：如何查找第N高的数据？

使用limit和offset，降序排列再返回第二条记录可以得到第二大的值。

limit n子句表示查询结果返回前n条数据

offset n表示跳过x条语句

limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据

\#将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资。

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

\#然而，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。

方法一：使用子查询，将结果作为临时表

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

方法二：使用ifnull函数

用判断空值的函数（ifnull）函数来处理特殊情况。

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

[DATEDIFF]: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff