sql常见问题

常用SQL

1、DENSE_RANK 开窗函数

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名 （Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换 句话说，名次之间不应该有“间隔”。 例如，根据上述给定的 T20191102表，你的查询应该返回（按分数从高到低排 列）：

ID Score Rank

3 89 1

4 80 2

1 80 2

2 75 3

5 72 4

参考答案 –MySQL 8.0和SQL Server

create table T20191102 (ID int, Score int );

insert into T20191102 values (1,80);

insert into T20191102 values (2,75);

insert into T20191102 values (3,89);

insert into T20191102 values (4,80);

insert into T20191102 values (5,71);

select ID,Score, DENSE_RANK() over(order by score desc) RK from T20191102

解析： DENSE_RANK()是一个排序开窗函数，它的特点是排序顺序即使有相同的，排序依然连续的， 区别于RANK()开窗函数（RANK()开窗函数是有相同排名则会跳跃不连续）

2、聚合函数的巧妙用法

有如下ABC三列和几组数据 想得到如下结果

A B C

aaa 3 1

bbb 7 X

ccc 11 Y

该如何写查询？

create table T20191103 (A varchar(20), B int, C varchar(20) );

insert into T20191103 values (‘aaa’,1,’X’);

insert into T20191103 values (‘aaa’,2,’Y’);

insert into T20191103 values (‘bbb’,3,’X’);

insert into T20191103 values (‘bbb’,4,’X’);

insert into T20191103 values (‘ccc’,5,’Y’);

insert into T20191103 values (‘ccc’,6,’Y’);

–MySQL 8.0和SQL Server

select a.A,a.B, case when D=2 then ‘1’ else E end AS C FROM ( SELECT A, SUM(B) B, COUNT(distinct C) as D, MIN(C) as E from T20191103 group by A) a

解析： 3 公众号：SQL数据库开发(id:sql_road)版权所有，盗版必究 1、 从题目要求来看，是要求对A列进行分组，对B列进行SUM求和，但是C列并非简单的聚 合。 2、 当同一组的C不同时返回1，当同一组的C相同是返回C。而有这种条件判定的通常我们 会使用CASE WHEN来进行求解。 3、 但是我们该如何判断C呢？这里的解法比较巧妙，当C不同的时候，我们发现他们去重 后的数量是2，C相同时，他们去重后的数量是1，因此我们可以通过C列去重后的数量 来判定。 4、 判定完之后，我们最后通过CASE WHEN的子查询即可完成条件转换输出了

3、查找连续出现问题

编写一个 SQL 查询，查找所有至少连续出现两次的数字。

例如，给定上面的 T20191104表， 1 和 2是连续出现至少两次的数字。

create table T20191104 ( ID int, Num int );

insert into T20191104 values (1,1);

insert into T20191104 values (2,1);

insert into T20191104 values (3,1);

insert into T20191104 values (4,2);

insert into T20191104 values (5,1);

insert into T20191104 values (6,2);

insert into T20191104 values (7,2);

select t.Num,count(*) Times FROM T20191104 t where exists ( select 1 from T20191104 a where a.Num=t.Num and (a.ID=t.ID+1 OR a.ID=t.ID-1) ) group by t.Num

解析： 1、 这个解法是连续性题目的其中一种解法，主要是通过关联子查询和自连接来比较每一 行的上下两行是否相等。 2、 如果相等就表示连续的，如果不相等就不连续，其中的where条件属于连续性判定中一 种固定写法： (a.ID=t.ID+1 OR a.ID=t.ID-1)

SQL 的一些进阶使用技巧

一、巧用 CASE WHEN 进行统计

来看看如何巧用 CASE WHEN 进行定制化统计,假设我们有如下的需求，希望根据左边各个市的人口统计每个省的人口

使用 CASE WHEN 如下

SELECT CASE pref_name
      WHEN'长沙'THEN'湖南'
      WHEN'衡阳'THEN'湖南'
      WHEN'海口'THEN'海南'
      WHEN'三亚'THEN'海南'
    ELSE'其他'ENDAS district,
    SUM(population) 
FROM PopTbl
GROUPBY district;

二、巧用 CASE WHEN 进行更新

现在某公司员人工资信息表如下:

现在公司出台了一个奇葩的规定

1. 对当前工资为 1 万以上的员工，降薪 10%。
2. 对当前工资低于 1 万的员工，加薪 20%。

一些人不假思索可能写出了以下的 SQL:

--条件1
UPDATE Salaries
SET salary = salary * 0.9 WHERE salary >= 10000;
--条件2
UPDATE Salaries
SET salary = salary * 1.2
WHERE salary < 10000;

这么做其实是有问题的， 什么问题，对小明来说，他的工资是 10500，执行第一个 SQL 后，工资变为 10500 * 0.9 = 9450, 紧接着又执行条件 2， 工资变为了 9450 * 1.2 = 11340，反而涨薪了！

如果用 CASE WHEN 可以解决此类问题，如下：

UPDATE Salaries
SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9
WHEN salary < 10000 THEN salary * 1.2
ELSE salary END;

三、巧用 HAVING 子句

一般 HAVING 是与 GROUP BY 结合使用的，但其实它是可以独立使用的， 假设有如下表，第一列 seq 叫连续编号，但其实有些编号是缺失的，怎么知道编号是否缺失呢，

用 HAVING 表示如下:

SELECT '存在缺失的编号' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

四、自连接

针对相同的表进行的连接被称为“自连接”(self join)，这个技巧常常被人们忽视，其实是有挺多妙用的

1、删除重复行

上图中有三个橘子，需要把这些重复的行给删掉，用如下自连接可以解决：

DELETE FROM Products P1
 WHERE id < ( SELECT MAX(P2.id) 
                   FROM Products P2 
                  WHERE P1.name = P2.name 
                    AND P1.price = P2.price ); 

2、排序

在 db 中，我们经常需要按分数，人数，销售额等进行排名，有 Oracle, DB2 中可以使用 RANK 函数进行排名，不过在 MySQL 中 RANK 函数未实现，这种情况我们可以使用自连接来实现,如对以下 Products 表按价格高低进行排名

使用自连接可以这么写:

-- 排序从 1 开始。如果已出现相同位次，则跳过之后的位次 
SELECT P1.name,
       P1.price,
       (SELECT COUNT(P2.price)
          FROM Products P2
         WHERE P2.price > P1.price) + 1 AS rank_1
  FROM Products P1 
  ORDER BY rank_1;

结果如下:

name price rank 
----- ------ ------ 
橘子    100     1 
西瓜     80     2 
苹果     50     3 
葡萄     50     3 
香蕉     50     3 
柠檬     30     6

五、巧用 COALESCE 函数

此函数作用返回参数中的第一个非空表达式，假设有如下商品，我们重新格式化一样，如果 city 为 null，代表商品不在此城市发行，但我们在展示结果的时候不想展示 null，而想展示 ‘N/A’, 可以这么做:

SELECT 
    COALESCE(city, 'N/A')
  FROM
    customers;

六、递归查询原理

SQL Server中的递归查询是通过CTE(表表达式)来实现。至少包含两个查询，第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点；第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

递归查询的终止条件

递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用MAXRECURION。

递归查询的优点

效率高，大量数据集下，速度比程序的查询快

递归的常见形式

WITH CTE AS (

SELECT column1,column2… FROM tablename WHERE conditions

UNION ALL

SELECT column1,column2… FROM tablename

INNER JOIN CTE ON conditions

)

递归查询示例

创建测试数据，有一个员工表Company，父级ID是部门ID的父节点，这是一个非常简单的层次结构模型。

USE SQL_Road
GO
CREATETABLE Company
(
    部门IDINT,
    父级IDINT,
    部门名称 VARCHAR(10)
)
INSERTINTO Company VALUES
(1,-1,'总部'),
(11,1,'财务中心'),
(12,1,'人力中心'),
(13,1,'信息中心'),
(111,11,'会计组'),
(112,11,'出纳组'),
(121,12,'薪酬组')

查询一下Company表里的数据

查询每个部门的直接上级ID

WITH CTE AS(
 SELECT 部门ID,父级ID,部门名称,部门名称 AS 父级部门名称
FROM Company
WHERE 父级ID=-1
UNION ALL
SELECT c.部门ID,c.父级ID,c.部门名称,p.部门名称 AS 父级部门名称
FROM CTE P
INNERJOIN Company c ON p.部门ID=c.父级ID
)

SELECT 部门ID,父级ID,部门名称,父级部门名称
FROM CTE

结果如下：

我们来解读一下上面的代码

1、查询父级ID=-1，作为根节点，这是递归查询的起始点。

2、迭代公式是 UNION ALL 下面的查询语句。在查询语句中调用中CTE，而查询语句就是CTE的组成部分，即 “自己调用自己”，这就是递归的真谛所在。

所谓迭代，是指每一次递归都要调用上一次查询的结果集，UNION ALL是指每次都把结果集并在一起。

3、迭代公式利用上一次查询返回的结果集执行特定的查询，直到CTE返回NULL或达到最大的迭代次数，默认值是32。最终的结果集是迭代公式返回的各个结果集的并集，求并集是由UNION ALL 子句定义的，并且只能使用UNION ALL

查询路径

下面我们通过层次结构查询子节点到父节点的PATH，我们对上面的代码稍作修改：

WITH CTE AS(
 SELECT 部门ID,父级ID,部门名称,CAST(部门名称 AS NVARCHAR(MAX)) AS 部门路径
FROM Company
WHERE 父级ID=-1
UNION ALL
SELECT c.部门ID,c.父级ID,c.部门名称,p.部门路径+'->'+c.部门名称 AS 部门路径
FROM CTE P
INNERJOIN Company c ON p.部门ID=c.父级ID
)

SELECT 部门ID,父级ID,部门名称,部门路径
FROM CTE

其中CAST(部门名称 AS VARCHAR(MAX))是将部门名称的长度设置为最大，防止字段过长超出字段长度。具体结果如下：

SQL 性能优化技巧

一、参数是子查询时，使用 EXISTS 代替 IN

如果 IN 的参数是（1，2，3）这样的值列表时，没啥问题，但如果参数是子查询时，就需要注意了。比如，现在有如下两个表：

现在我们要查出同时存在于两个表的员工，即田中和铃木，则以下用 IN 和 EXISTS 返回的结果是一样，但是用 EXISTS 的 SQL 会更快:

-- 慢
SELECT * 
FROM Class_A
WHEREidIN (SELECTid
               FROM  CLASS_B);

-- 快
SELECT *
FROM Class_A A 
WHERE EXISTS
(SELECT * 
   FROM Class_B  B
WHERE A.id = B.id);

为啥使用 EXISTS 的 SQL 运行更快呢，有两个原因

1. 可以`用到索引，如果连接列 (id) 上建立了索引，那么查询 Class_B 时不用查实际的表，只需查索引就可以了。
2. 如果使用 EXISTS，那么只要查到一行数据满足条件就会终止查询， 不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样

另外如果 IN 后面如果跟着的是子查询，由于 SQL 会先执行 IN 后面的子查询，会将子查询的结果保存在一张临时的工作表里（内联视图），然后扫描整个视图，显然扫描整个视图这个工作很多时候是非常耗时的，而用 EXISTS 不会生成临时表。

当然了，如果 IN 的参数是子查询时，也可以用连接来代替，如下：

-- 使用连接代替 IN SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;

用到了 「id」列上的索引，而且由于没有子查询，也不会生成临时表

二、避免排序

SQL 是声明式语言，即对用户来说，只关心它能做什么，不用关心它怎么做。这样可能会产生潜在的性能问题：排序，会产生排序的代表性运算有下面这些

• GROUP BY 子句
• ORDER BY 子句
• 聚合函数(SUM、COUNT、AVG、MAX、MIN)
• DISTINCT
• 集合运算符(UNION、INTERSECT、EXCEPT)
• 窗口函数(RANK、ROW_NUMBER 等)

如果在内存中排序还好，但如果内存不够导致需要在硬盘上排序上的话，性能就会急剧下降，所以我们需要减少不必要的排序。怎样做可以减少排序呢。

1、 使用集合运算符的 ALL 可选项

SQL 中有 UNION，INTERSECT，EXCEPT 三个集合运算符，默认情况下，这些运算符会为了避免重复数据而进行排序，对比一下使用 UNION 运算符加和不加 ALL 的情况:

注意：加 ALL 是优化性能非常有效的手段，不过前提是不在乎结果是否有重复数据。

2、使用 EXISTS 代表 DISTINCT

为了排除重复数据， DISTINCT 也会对结果进行排序，如果需要对两张表的连接结果进行去重，可以考虑用 EXISTS 代替 DISTINCT，这样可以避免排序。

如何找出有销售记录的商品，使用如下 DISTINCT 可以：

SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;

不过更好的方式是使用 EXISTS:

SELECT item_no FROM Items I
WHERE EXISTS 
        (SELECT *
           FROM SalesHistory SH
          WHERE I.item_no = SH.item_no);

既用到了索引，又避免了排序对性能的损耗。

二、在极值函数中使用索引（MAX/MIN）

使用 MAX/ MIN 都会对进行排序，如果参数字段上没加索引会导致全表扫描，如果建有索引，则只需要扫描索引即可，对比如下

-- 这样写需要扫描全表 
SELECT MAX(item)
  FROM Items;

-- 这样写能用到索引 
SELECT MAX(item_no)
  FROM Items;

注意：极值函数参数推荐为索引列中并不是不需要排序，而是优化了排序前的查找速度（毕竟索引本身就是有序排列的）。

三、能写在 WHERE 子句里的条件不要写在 HAVING 子句里

下列 SQL 语句返回的结果是一样的:

-- 聚合后使用 HAVING 子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory GROUPBY sale_date
HAVING sale_date = '2007-10-01';

-- 聚合前使用 WHERE 子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUPBY sale_date;

使用第二条语句效率更高，原因主要有两点

1. 使用 GROUP BY 子句进行聚合时会进行排序，如果事先通过 WHERE 子句能筛选出一部分行，能减轻排序的负担
2. 在 WHERE 子句中可以使用索引，而 HAVING 子句是针对聚合后生成的视频进行筛选的，但很多时候聚合后生成的视图并没有保留原表的索引结构

四、在 GROUP BY 子句和 ORDER BY 子句中使用索引

GROUP BY 子句和 ORDER BY 子句一般都会进行排序，以对行进行排列和替换，不过如果指定带有索引的列作为这两者的参数列，由于用到了索引，可以实现高速查询，由于索引是有序的，排序本身都会被省略掉

五、使用索引时，条件表达式的左侧应该是原始字段

假设我们在 col 列上建立了索引，则下面这些 SQL 语句无法用到索引

SELECT *
  FROM SomeTable
 WHERE col * 1.1 > 100;

SELECT *
  FROM SomeTable
 WHERE SUBSTR(col, 1, 1) = 'a';

以上第一个 SQL 在索引列上进行了运算, 第二个 SQL 对索引列使用了函数，均无法用到索引，正确方式是把列单独放在左侧,如下:

SELECT *
  FROM SomeTable
 WHERE col_1 > 100 / 1.1;

当然如果需要对此列使用函数，则无法避免在左侧运算，可以考虑使用函数索引，不过一般不推荐随意这么做。

六、尽量避免使用否定形式

如下的几种否定形式不能用到索引：

• <>
• !=
• NOT IN

所以以下 了SQL 语句会导致全表扫描

SELECT *
  FROM SomeTable
 WHERE col_1 <> 100;

可以改成以下形式

SELECT *
  FROM SomeTable
 WHERE col_1 > 100 or col_1 < 100;

七、进行默认的类型转换

假设 col 是 char 类型，则推荐使用以下第二，三条 SQL 的写法，不推荐第一条 SQL 的写法

× SELECT * FROM SomeTable WHERE col_1 = 10;
○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

虽然第一条 SQL 会默认把 10 转成 ‘10’，但这种默认类型转换不仅会增加额外的性能开销，还会导致索引不可用，所以建议使用的时候进行类型转换。

八、减少中间表

在 SQL 中，子查询的结果会产生一张新表，不过如果不加限制大量使用中间表的话，会带来两个问题，一是展示数据需要消耗内存资源，二是原始表中的索引不容易用到，所以尽量减少中间表也可以提升性能。

九、灵活使用 HAVING 子句

这一点与上面第八条相呼应，对聚合结果指定筛选条件时，使用 HAVING 是基本的原则，可能一些工程师会倾向于使用下面这样的写法:

SELECT *
  FROM (SELECT sale_date, MAX(quantity) AS max_qty
          FROM SalesHistory 
         GROUP BY sale_date) TMP
         WHERE max_qty >= 10;

虽然上面这样的写法能达到目的，但会生成 TMP 这张临时表，所以应该使用下面这样的写法:

SELECT sale_date, MAX(quantity) 
  FROM SalesHistory
 GROUP BY sale_date
HAVING MAX(quantity) >= 10;

HAVING 子句和聚合操作是同时执行的，所以比起生成中间表后再执行 HAVING 子句，效率会更高，代码也更简洁

十、需要对多个字段使用 IN 谓词时，将它们汇总到一处

一个表的多个字段可能都使用了 IN 谓词，如下:

SELECT id, state, city 
  FROM Addresses1 A1
 WHERE state IN (SELECT state
                   FROM Addresses2 A2
                  WHERE A1.id = A2.id) 
    AND city IN (SELECT city
                   FROM Addresses2 A2 
                  WHERE A1.id = A2.id);

这段代码用到了两个子查询，也就产生了两个中间表，可以像下面这样写

SELECT *
  FROM Addresses1 A1
 WHERE id || state || city
 IN (SELECT id || state|| city
       FROM Addresses2 A2);

这样子查询不用考虑关联性，没有中间表产生，而且只执行一次即可

其他技巧mysql

查日志
show variables like ‘%slow_query_log%’;
set global slow_query_log=1;

set global long_query_time=1;

arthas监控程序，一个开源的Java诊断工具
thread -n 5

prometheus服务器资源监控

rabbitmq-perf-test是RabbitWQ官方提供的性能测试工具，用于评估RabbitMQ的消息吞吐量，延迟和系统资源消耗

1.left join后用 on还是where 都区别

通过 A left B join on and 后面的条件来使查出的两条记录变成一条，奈何发现还是有两条。

后来发现 join on and 不会过滤结果记录条数，只会根据and后的条件是否显示 B表的记录，A表的记录一定会显示。

不管and 后面的是A.id=1还是B.id=1,都显示出A表中所有的记录，并关联显示B中对应A表中id为1的记录或者B表中id为1的记录。

在使用left join时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner join没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

mycli，一个使用python编写的终端工具，支持语法高亮，自动补全，多行模式，并且如果你熟悉vi的话，可以使用vi-mode快速移动，编辑。总之，vi + mycli 简直是神器！

同样， postgreSQL 可以使用pgcli。b

pip install -U mycli    # 默认你已经安装了pip

2.关联联接

虽然两个表拥有公共字段便可以创建联接，但是使用外键可以更好地保证数据完整性。比如当对一个学生插入一条不存在的班级的时候，便会插入失败。一般来说，联接比子查询拥有更好的性能。

# 列出软件工程班级中的学生
select * from student, class
where student.class_id = class.id and class.name = '软件工程';

check

检查约束可以使列满足特定的条件，如果学生表中所有的人的年龄都应该大于0。

不过很可惜mysql不支持，可以使用触发器代替

alter table student add constraint check (age > 0);

如何根据一个表的数据更新另一个表

比如以上 student 表保存着成绩，另有一表 score_correct 内存因失误而需修改的学生成绩。

在mysql中，可以使用如下语法

update 
student, 
score_correct 
set student.score = score_correct.score 
where student.id = score_correct.uid;

3. 索引是如何工作的

简单来说，索引分为 hash 和 B-Tree 两种。hash 查找的时间复杂度为O(1)。B-Tree 其实是 B+Tree，一种自平衡多叉搜索数，自平衡代表每次插入和删除数据都会需要动态调整树高，以降低平衡因子。B+Tree 只有叶子节点会存储信息，并且会使用链表链接起来。因此适合范围查找以及排序，不过只能搜索最左前缀，如只能索引以 a开头的姓名，却无法索引以 a结尾的姓名。另外，Everything is trade off。B+Tree的自平衡特性保证能够快速查找的同时也降低了更新的性能，需要权衡利弊。

4. 如何联接多个行的字段

在mysql中，可以使用 group_concat

select group_concat(name) from student;

5. 如何在一个sql语句中插入多行数据

values 使用逗号相隔，可以插入多行数据

insert into student(id, name) values (), (), ()

如何在 select中使用条件表达式

示例，在student表中，查询所有人成绩，小于60则显示为0

select id, name, if(score < 60, 0, score) score from student;

6. 如何找到重复项

select name, sex, count(*) times from student
group by name, sex
having times > 1;

7. 什么是SQL注入

如有一条查询语句为

"select * from (" + table + ");"

当table取值 student);drop table student;-- 时，语句变为了，会删掉表，造成攻击。

"select * from (student); drop table student; --);"

8、ORDER BY高级用法

一、ORDER BY返回的是游标而不是集合

但是*对于带有排序作用的ORDER BY子句的查询，它返回的是一个对象，其中的行按特定的顺序组织在一起，我们把这种对象称为***游标

二、ORDER BY子句是唯一能重用列别名的一步

这里涉及SQL语句的语法顺序和执行顺序了，我们常见的SQL语法顺序如下：

具体执行顺序如下(关键字前面的数字代表SQL执行的顺序步骤)：

(10)SELECT (11)DISTINCT (7)SUM(9)OVER() (13) (1)FROM [left_table](3) JOIN (2) ON (4)WHERE AND(5)GROUP BY (6)WITH <CUBE | RollUP>(8)HAVING (12)ORDER BY

(13)Limit/offset

从上面可以看到SELECT在HAVING后才开始执行，这个时候SELECT后面列的别名只对后续的步骤生效，而对SELECT前面的步骤是无效的。所以如果你在WHERE，GROUP BY，或HAVING后面使用列的别名均会报错

表表达式不能使用ORDER BY排序

表表达式包括视图，内联表值函数，派生表(子查询)和公用表表达式(CTE)。

例如下面的视图是无效的

CREATE VIEW V_Customers AS
SELECT 
客户ID AS ID,
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM Customers
ORDER BY ID,Name,Address

因为视图，内联表值函数，派生表(子查询)和公用表表达式(CTE)等返回的结果还需要进一步的去使用，加了ORDER BY进行排序是多此一举，反而会浪费系统资源。所以数据库的开发者不希望大家使用这样不规范操作。

报错信息中可以看到：在另外还指定了 TOP、OFFSET 或 FOR XML是可以使用ORDER BY的。

SQL 练习

1、根据班级学生的分数进行排名，如果分数相等则为同一名次

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

在where以及排序中经常用到的字段需要添加Btree索引，因此 score 上可以添加索引。

2. 写一个函数，获取第 N 高的分数

create function getNthHighestScore(N int) returnint
begin
declare M intdefault N-1;
  return (
    selectdistinct score from student 
    orderby score desclimit M, 1;
  )
end;

select getNthHighestScore(2);

3. 检索每个班级分数前两名学生，并显示排名

select 
class.id class_id, 
class.name class_name, 
s.name student_name, 
score, 
rank
from (
select *, 
(
    selectcount(distinct score) from student s2 
where s2.score >= s1.score 
and s2.class_id = s1.class_id
  ) asrank
from student s1
) as s 
leftjoinclasson s.class_id = class.id 
whererank <= 2;

--如果不想在from中包含select子句，也可以像如下检索，不过不显示排名
select
class.id class_id, 
class.name class_name, 
s1.name name, 
score
from student s1 
leftjoinclasson s1.class_id = class.id
where (
selectcount(*) from student s2 
where s2.class_id = s1.class_id 
and s1.score <= s2.score) <= 2
orderby s1.class_id, score desc;

Result:

class_name	student_name	score	rank
软件工程	张三	100	1
软件工程	王五	99	2
市场营销	燕七	34	2
市场营销	林仙儿	78	1