sql 学习笔记

最新推荐文章于 2024-07-31 19:38:21 发布

this-is-hhh

最新推荐文章于 2024-07-31 19:38:21 发布

阅读量509

点赞数

分类专栏：笔记文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/a1343367439/article/details/122560724

版权

笔记专栏收录该内容

3 篇文章 0 订阅

订阅专栏

case when then end 语句

case when 条件1 then 结果1 when 条件2 then 结果2 .......... else 结果n end

选择最后一个数据的id

id = (select count(*) from 表名) //选择最后一个数据的id

leetcode626

Input: 
Seat table:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
Output: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
Explanation: 
Note that if the number of students is odd, there is no need to change the last one's seat.

# Write your MySQL query statement below
select (
    case 
        when id % 2 = 1 and id = (select count(*) from seat) then id
        when id % 2 = 1 then id + 1
        else id - 1
    end
) as id , student
from Seat 
order by id;

select 
    if(id%2=0,
        id-1,
        if(id=(select count(distinct id) from seat),
            id,
            id+1)) 
    as id,student 
from seat 
order by id;

datediff函数：可以返回两个日期的差值。