SQL练习（9）

sql
create table Employee(
       id int primary key,
       salary int
);

insert into Employee values (4,400),(5,500),(6,600);


select * from Employee;


select (select distinct salary
        from Employee order by
        salary desc limit 1 offset 2)
        as SecondHighestSalary;

select max(salary) from Employee
where salary < (select max(salary)
from Employee);


select ifnull((select distinct salary
        from Employee order by
        salary desc limit 1 offset 1),null)
        as SecondHighestSalary;


create function getNthHighestSalary(n int) returns int
    DETERMINISTIC
begin
declare m int;
    set m = n-1;
    return(
        select distinct salary
        from Employee order by
        salary desc limit 1 offset m
);
end;

drop function getNthHighestSalary;

select getNthHighestSalary(4);

总结一

sql


create table Scores(
    id int primary key ,
    score int
);



insert into Scores values (1,50),(2,55),(3,69),(4,50),(5,43),(6,55);
select * from Scores;



select s1.score, (select count(distinct s2.score)
from Scores s2 where s2.score >= s1.score) 
as 'rank' from Scores as s1 order by s1.score desc ;



select s1.score, (select count(distinct s2.score)
from Scores s2 where s2.score >= s1.score) as 'rank'
from Scores as s1;



SELECT
    S.score,
    dense_rank() OVER (ORDER BY S.score DESC) AS `rank`

FROM Scores AS S;

MySQL 8.0版本引入了窗口函数（Window Function），它是一种强大的查询工具，能够对数据集进行分组、排序和聚合操作，并且可以在结果集中的每一行上进行计算。下面详细介绍MySQL中的所有窗口函数。

ROW_NUMBER(): 为结果集中的每一行分配一个唯一的整数值，表示行的序号。
RANK(): 对结果集中的行进行排序，并为每一行分配一个排名值。如果有相同的值，则排名相同，下一个排名将被跳过。
DENSE_RANK(): 类似于RANK()函数，但是如果有相同的值，则排名相同，下一个排名不会被跳过。
NTILE(n): 将结果集划分为指定数量的桶（bucket），并为每一行分配一个桶号。比如，NTILE(4)将结果集划分为4个桶，每个桶中包含相同数量的行。
LEAD(expression, offset, default): 返回当前行之后指定偏移量的行的值。如果偏移量超出结果集的范围，则返回默认值。默认偏移量为1。
LAG(expression, offset, default): 返回当前行之前指定偏移量的行的值。如果偏移量超出结果集的范围，则返回默认值。默认偏移量为1。
FIRST_VALUE(expression): 返回结果集中第一行的指定表达式的值。
LAST_VALUE(expression): 返回结果集中最后一行的指定表达式的值。
NTH_VALUE(expression, n): 返回结果集中指定位置（n）的行的指定表达式的值。

这些窗口函数可以与OVER子句一起使用，用于定义窗口范围（Window Frame），以确定窗口函数计算的行的范围。常见的窗口范围包括ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从开始到当前行）、ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING（从开始到结束）等。

窗口函数的语法示例：

sql
SELECT 
    column1,
    column2,
    ROW_NUMBER() OVER (ORDER BY column1) AS row_number,
    RANK() OVER (ORDER BY column2) AS rank,
    DENSE_RANK() OVER (ORDER BY column2) AS dense_rank,
    NTILE(4) OVER (ORDER BY column1) AS ntile,
    LEAD(column1) OVER (ORDER BY column1) AS lead_value,
    LAG(column1) OVER (ORDER BY column1) AS lag_value,
    FIRST_VALUE(column1) OVER (ORDER BY column1) AS first_value,
    LAST_VALUE(column1) OVER (ORDER BY column1) AS last_value,
    NTH_VALUE(column1, 3) OVER (ORDER BY column1) AS nth_value
FROM your_table;

这个示例展示了如何在查询中使用窗口函数。你需要将your_table替换为你的实际表名，并根据需要选择和调整窗口函数及其参数。

目录

总结一