leetcode题

626. Exchange Seats

玛丽是一所中学的老师，她又一个表为 seat ,上面有学生的名字和相应的座位号。
列id是连续增量。
玛丽想给邻座的学生换座位。
你能写一个SQL查询来为玛丽输出结果吗

image.png

输出结果

image.png

代码如下

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;

197. Rising Temperature

Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates. image.png 输出如下 image.png

代码：

SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.RecordDate, w.RecordDate) = 1
        AND weather.Temperature > w.Temperature;

DATEDIFF():计算两个日期之差。