炼码LintCode--数据库题库（级别：中等；数量：更新中~）--刷题笔记_03

炼码LintCode–数据库题库（级别：中等；数量：更新中~）–刷题笔记_03

炼码LintCode–数据库题库（级别：入门；数量：144道）–刷题笔记_01

炼码LintCode–数据库题库（级别：简单；数量：55道）–刷题笔记_02

持续更新~~

3617 · 更换连续两个人的座位（case when）

3617 · 更换连续两个人的座位

题：

sql：

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 , name
from seat
order by id;

解释：

-- 比如数据长这样：   id = 1 , name = a  ;
                --   id = 2 , name = b  ;
                --   id = 3 , name = c  ;
                --   id = 4 , name = d  ;
                --   ...
                --   id = 11 , name = k ;


--        id = 1 , 对应的 name = a，那么符合第二个when，id = 1 + 1 = 2 ，此时该条记录就变成 id = 2 ， name = a；
--        id = 2 , 对应的 name = b ， 那么符合else这个判断，id = 2 - 1 = 1   , 此时该条记录就变成 id = 1 ， name = b
-- 此时，id = 1 和 id = 2 的数值就改变了，后面再根据 order by id 进行排序，就变成 id = 1 ，name = b;
                                                                        --  id = 2 , name = a;   这样就实现了位置的互换
                                                                        
-- 最后那条记录如果是偶数，那么就正常交换，如果是奇数，那么就符合第一个 when 的判断，id不做任何改变，也就是位置不进行交换。

select
(
    -- 这里的逻辑就是改变id的数值而已。
    case 
        -- 如果id是奇数且id=表的记录的，说明这个id是最后一条记录的id，因为是奇数，所以位置不需要改变，id不变。
        when id % 2 = 1 and id = (select count(*) from seat) then id
        -- 接下来的id就不是最后一条记录了，位置就得改变，如果 id 是奇数 ，那么 id + 1 
        when id % 2 = 1 then id + 1
        -- 否则，偶数的id - 1 变成奇数，而且是上一条记录的数据的id
        else id - 1
    end     
) as id , name

from seat

order by id;

3615 · 数据中位数（窗口函数、floor、ceil 取整函数）

3615 · 数据中位数

题：

sql：

select avg(num) median
from (
    select 
        num ,
        row_number() over(order by num) as 'row_num', 
        count(*) over () as 'total_rows'
    from Number
) subquery
where row_num in (  
    floor( (total_rows + 1) / 2 ), ceil( (total_rows + 1) / 2 )
);
 

解释：

就是先把数据排序，然后给每个数据一个编号，然后再用总条数去+1除2得出中间数。
总条数是偶数，+1再除2，得出中间两个数，再算平均值，得出的就是中位数；
总条数是奇数，+1再除2，得出的就是两个一样的数值，取平均数，得出的就是中位数。

-- floor 和 ceil 是两个常见的数学函数，它们分别用来处理数字的取整


-- avg 是求平均数，也就是求符合条件的中位数
select avg(num) median
from (
    select 
        num ,
        -- 先给 num 降序排序，然后再给每个数值一个编号
        row_number() over(order by num) as 'row_num', 
        -- 这里是统计表中有几条数据，用这个窗口函数，可以每一行都显示表总条数，而且不需要和group by 搭配
        -- 使用窗口函数时，可以返回每一行对应的总行数，而不需要对结果进行聚合。
        count(*) over () as 'total_rows'
    from Number
) subquery
where row_num in (  
    -- floor(x)：返回小于或等于 x 的最大整数，即向下取整。 floor(3.5)  = 3 ;
    -- ceil(x)：返回大于或等于 x 的最小整数，即向上取整。  ceil(3.5) = 4 ;
    floor( (total_rows + 1) / 2 ), ceil( (total_rows + 1) / 2 )
);
 

3612 · 指定日期的产品价格（coalesce 函数）

3612 · 指定日期的产品价格

题：

sql：

SELECT 
    p.id,
    COALESCE(
        (SELECT new_price
         FROM Products 
         WHERE id = p.id AND updated_date <= '2023-05-15'
         ORDER BY updated_date DESC
         LIMIT 1), 99) AS price
FROM 
    (SELECT DISTINCT id FROM Products) p;

解释：

-- coalesce 函数:
-- 语法： COALESCE(expression1, expression2, ..., expressionN)
-- expression1, expression2, ..., expressionN 是一个或多个表达式（列、常量、计算结果等）。
-- COALESCE 会从左到右依次检查这些表达式或值，返回第一个 非 NULL 的值。如果所有的表达式都是 NULL，它会返回 NULL。
-- 值都为null时，也可以返回我们指定的数据


SELECT 
    p.id,
    -- COALESCE 函数用于确保当子查询没有返回任何结果（即该产品在 2023-05-15 前没有任何修改记录）时，返回默认价格 99
    COALESCE(
        (SELECT new_price
         FROM Products 
         WHERE id = p.id AND updated_date <= '2023-05-15'
         -- 最新时间--用于获取最新修改的记录
         ORDER BY updated_date DESC
         LIMIT 1), 99) AS price
FROM 
    -- 先进行去重，得到所有的商品 id
    (SELECT DISTINCT id FROM Products) p;

3611 · 我的最佳好友

3611 · 我的最佳好友

题：

sql：

解释：

xxx

题：

sql：

解释：

xxx

题：

sql：

解释：

xxx

题：

sql：

解释：

xxx

题：

sql：

解释：

未完待续~~~