LeetCode算法题整理（sql篇）sql

Posted on   |   Updated on | In | | Visitors

595. Big Countries

查询’大城市’。原题

  • 使用where OR

    1
    2
    3
    select name, population, area 
    from World 
    where area > 3000000 or population > 25000000;

  • 使用UNION

    1
    2
    3
    4
    5
    6
    7
    8
    select name, population, area 
    from World 
    where area > 3000000
    union
    select name, population, area
    from World
    where population > 25000000
    ;

补充说明：Solution中解释道，使用UNION会比OR快上一丢丢。

Suppose we are searching population and area, Given that MySQL usually uses one one index per table in a given query, so when it uses the 1st index rather than 2nd index, it would still have to do a table-scan to find rows that fit the 2nd index.

因为前者查询的时候只用到第一个索引，对第二个条件查询时，也就是population，使用的是全表扫描，于是浪费了一些时间。但是想想背后的代价，想必是以空间来换时间。

627. Swap Salary

转换性别。原题

  • 使用if

    1
    2
    3
    UPDATE salary
    SET sex=IF(sex='m', 'f', 'm')
    ;

  • 使用CASE

    1
    2
    3
    4
    5
    6
    7
    update salary
    set 
        sex = CASE sex
        when 'm' Then 'f'
        else 'm'
        END
    ;

  • 使用XOR

    1
    2
    3
    update salary
    set sex = CHAR(ASCII('f') ^ ASCII('m') ^ ASCII(sex))
    ;

620. Not Boring Movies

使用MOD替代%。原题

1
2
3
4
5
select *
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating DESC
;

182. Duplicate Emails

查询多余的重复值。原题

思考：开始陷入了误区，以为要将重复的id也查出来。

1
2
3
4
SELECT email
FROM   Person
GROUP  BY email
HAVING Count(*) > 1;

175. Combine Two Tables

左链接。过于简单。原题

1
2
3
4
5
6
SELECT p.FirstName, p.LastName,
    ad.City, ad.State
FROM Person p
    LEFT JOIN Address ad
        ON p.PersonId = ad.PersonId
;

181. Employees Earning More Than Their Managers

自链接。原题

1
2
3
4
5
6
select e.name Employee
from Employee e
    inner join Employee m
        on e.ManagerId = m.Id
where e.Salary > m.Salary
;

183. Customers Who Never Order

没有订单的顾客。原题

1
2
3
4
5
6
select c.name Customers
from Customers c
    left join Orders o
        on c.id = o.CustomerId
where o.CustomerId is Null
;
1
2
3
4
5
6
select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders
);

596. Classes More Than 5 Students

超过5个学生的班级。原题

1
2
3
4
5
select class
from courses
group by class
having count(distinct student) >= 5
;

197. Rising Temperature

这个挺有意思，开始天真地想用id作为条件，测试用例教做人。原题

  • 我的答案

    1
    2
    3
    4
    5
    6
    SELECT today.Id
    FROM   Weather yest
           INNER JOIN Weather today
                  ON today.RecordDate = DATE_ADD(yest.RecordDate, INTERVAL 1 day)
    WHERE  today.Temperature > yest.Temperature
    ;

  • 标准答案

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
        weather.id AS 'Id'
    FROM
        weather
            JOIN
        weather w ON DATEDIFF(weather.date, w.date) = 1
            AND weather.Temperature > w.Temperature
    ;

    查看Mysql文档：

    DATEDIFF(expr1,expr2)

    DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

    1
    2
    3
    4
    mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
            -> 1
    mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
            -> -31

196. Delete Duplicate Emails

删除重复的邮件。原题

1
2
3
4
delete p1 
from Person p1, Person p2
where p1.email=p2.email and p1.Id > p2.Id
;

176. Second Highest Salary

返回第二高的salary。原题

PS：奇怪的题，没有记录非要人返回Null，只能再套一层查询或者使用IFNULL也可以。

1
2
3
4
5
6
7
8
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

626. Exchange Seats

交换相邻两行的值。原题

开始理解错了，以为要update。

  • CASE

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT ( CASE
               WHEN MOD(id, 2) != 0
                    AND counts != id THEN id + 1
               WHEN MOD(id, 2) != 0
                    AND counts = id THEN id
               ELSE id - 1
             end ) AS id,
           student
    FROM   seat,
           (SELECT Count(*) AS counts
            FROM   seat) AS seat_counts
    ORDER  BY id ASC;;

  • COALESCE()

    • 第一步：使用XOR，但是不能直接使用排序因为id为5的被换成了6。

      1
      SELECT id, (id+1)^1-1, student FROM seat;
      1
      2
      3
      4
      5
      6
      7
      8
      9
      +------+------+----------+------------+---------+
      | id   | id+1 | (id+1)^1 | (id+1)^1-1 | student |
      +------+------+----------+------------+---------+
      |    1 |    2 |        3 |          2 | Abbot   |
      |    2 |    3 |        2 |          1 | Doris   |
      |    3 |    4 |        5 |          4 | Emerson |
      |    4 |    5 |        4 |          3 | Green   |
      |    5 |    6 |        7 |          6 | Jeame   |
      +------+------+----------+------------+---------+

      • 第二步：使用LEFT JOIN链接。

        1
        2
        3
        4
        5
        SELECT *
        FROM   seat s1
               LEFT JOIN seat s2
                      ON ( s1.id + 1 )^1 - 1 = s2.id
        ORDER  BY s1.id;
        1
        2
        3
        4
        5
        6
        7
        8
        9
        +------+---------+------+---------+
        | id   | student | id   | student |
        +------+---------+------+---------+
        |    1 | Abbot   |    2 | Doris   |
        |    2 | Doris   |    1 | Abbot   |
        |    3 | Emerson |    4 | Green   |
        |    4 | Green   |    3 | Emerson |
        |    5 | Jeame   | NULL | NULL    |
        +------+---------+------+---------+

    • 第三步：使用COALESCE()

      1
      2
      3
      4
      5
      6
      7
      SELECT s1.id,
             Coalesce(s2.student, s1.student) as student
      FROM   seat s1
             LEFT JOIN seat s2
                    ON ( s1.id + 1 )^1 - 1 = s2.id
      ORDER  BY s1.id
      ;

参考：Mysql文档

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
The return type of COALESCE() is the aggregated type of the argument types.

178. Rank Scores