LeetCode Database

262. 行程和用户

解答

# Write your MySQL query statement below
SELECT Request_at AS "Day", round(avg(Status != "completed"), 2) AS "Cancellation Rate"
FROM Trips JOIN Users u1 ON (Client_Id = u1.Users_Id AND u1.Banned = "No") JOIN Users u2 ON (Client_Id = u2.Users_Id AND u2.Banned = "No")
WHERE Request_at BETWEEN "2013-10-01" AND "2013-10-03"
GROUP BY Request_at
ORDER BY Request_at

思路

首先使用两个JOIN将一个Trips表和两个Users表连接起来,相当于分别跟用户表和司机表连接在同一张表上,该表除了包含形成信息,还确保了行程中的双方的状态都是Not Banned。

之后使用WHERE选择日期,使用了"BETWEEN... AND..."的语法。使用GROUP BY 使得同一天的信息被汇总,使用avg对同一天的信息进行选择。avg会自动算某一列的平均值,这道题中avg括号中加了一个逻辑判断,表示计算满足这个状态占总状态的平均值。这是一个很trick的语法,需要注意。

627. 交换工资

解答

UPDATE salary
SET sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
        END;

思路

这道题的要点在使用"CASE...[WHEN...THEN...](ELSE...)END"的语法,并且使用UPDATE去更新表中的数值。

176. 第二高的薪水

解答

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

思路

这道题的要点有两个。第一个是LIMIT语法,"LIMIT x"表示限制只选择前x行数据,"LIMIT x, y"表示从第x行开始,输出y行数据。第二个是IFNULL语法,"IFNULL(x, y)"表示如果x是NULL,输出y,否则输出x的值。

197. 上升的温度

解答

SELECT W1.Id
FROM Weather W1, Weather W2
WHERE W1.Temperature > W2.Temperature AND (DATEDIFF(W1.RecordDate, W2.RecordDate) = 1)

思路

这道题主要学习DATEDIFF()函数,DATEDIFF(x, y)表示计算x和y两个日期变量的差值,如x=2013-1-5和y=2013-1-2后则输出3。

1179. 重新格式化部门表

解答

SELECT id, 
SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id

思路

首先要明确这是一道数据整合的问题,需要用到GROUP BY 来将一个部门的数据整合到一行内。在SELECT中使用CASE语句来条件式地选择原有表中的特定数据。需要特别注意的是,sql为GROUP BY 中的每个元素创建一组结果,并且仅允许针对多个值(SUM, AVG, MIN, MAX, COUNT)进行聚合操作。因此如果使用了GROUP BY函数,在SELECT语句里面要加入聚合函数以将结果转为单个值。

178. 分数排名

解答

SELECT Score, (
    SELECT COUNT(DISTINCT s2.Score) + 1
    FROM Scores s2
    WHERE s2.Score > s1.Score
) AS 'Rank'
FROM Scores s1
ORDER BY s1.Score DESC

思路

在SELECT中加入子查询并使用COUNT来计算排名。

177. 第N高的薪水

解答

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 N, 1), NULL) AS getNthHighestSalary
  );
END

620. 有趣的电影

解答

SELECT id, movie, description, rating
FROM cinema
WHERE (id%2 > 0) AND NOT (description = 'boring')
ORDER BY rating DESC

601. 体育馆的人流量

解答1

SELECT DISTINCT(s1.id), s1.visit_date, s1.people
FROM stadium s1, stadium s2, stadium s3
WHERE (s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100) AND (
    (s1.id = s2.id-1 AND s2.id = s3.id-1) OR
    (s1.id = s3.id-1 AND s3.id = s2.id-1) OR
    (s2.id = s1.id-1 AND s1.id = s3.id-1) OR
    (s2.id = s3.id-1 AND s3.id = s1.id-1) OR
    (s3.id = s1.id-1 AND s1.id = s2.id-1) OR
    (s3.id = s2.id-1 AND s2.id = s1.id-1)
)
ORDER BY id ASC

解答2

with rktable AS (
    SELECT id, visit_date, people, id-rank() over(order by id) AS rk
    FROM stadium
    WHERE people >= 100
    ORDER BY id
)

SELECT id, visit_date, people
FROM rktable
WHERE rk in (
    SELECT rk
    FROM rktable
    GROUP BY rk
    HAVING COUNT(*) >= 3
)

思路

第一种解答比较朴素,就不讲了。

第二种解答需要用到开窗函数即over(),over函数中可以用到两种语法,一是order by,用于排序,另一种是partition by,用来分组,无论用到哪种语法都需要映射到前面的语句中。比如这里使用的是rank函数,那么后面over中指定排序方式就映射到前面的rank()函数中。

首先将人数大于100的记录筛选出来,并加上一个id减筛选后的序列的rk值,对于这道题的特殊性来说,连续大于100人的日期中的rk值是相同的,并且随着日期值递增rk值是严格单调递增的,因此可以使用rk值的数目来判断连续拥挤的天数。最后使用一个子查询找到相同rk值大于3的记录,使用了GROUP BY + HAVING 的语法。HAVING和WHERE一样是条件筛选语句,区别在WHERE使用在筛选单调记录,HAVING用在筛选GROUP BY后的组。

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容