LeetCode做题记录（持续更新）

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