每日一题SQL

以下题目来源微信公众号【SQL数据库开发】

1、编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

排序函数：

1. ROW_NUMBER() 1 2 3 4..

2. DENSE_RANK() 1 2 2 3 4...

3. RANK() 1 2 2 4 5...

---创建临时练习表
DROP TABLE SC_20231215;
-- 创建表
CREATE TABLE SC_20231215(
ID NUMBER(20),
SCORE INT NOT NULL
);
commit;

select * from SC_20231215;
INSERT INTO SC_20231215 (ID,SCORE) VALUES('1',80);
COMMIT;
INSERT INTO SC_20231215 (ID,SCORE) VALUES('2',75);
COMMIT;
INSERT INTO SC_20231215 (ID,SCORE) VALUES('3',89);
COMMIT;
INSERT INTO SC_20231215 (ID,SCORE) VALUES('4',80);
COMMIT;
INSERT INTO SC_20231215 (ID,SCORE) VALUES('5',71);
COMMIT;

SELECT ID,SCORE,DENSE_RANK() OVER(ORDER BY SCORE DESC) RANK
FROM SC_20231215;

2、有如下ABC三列和几组数据

想得到如下结果

该如何写查询？

提示：可以使用聚合函数或者lag函数来求解

---创建临时练习表
DROP TABLE ABC_20231215;
create table ABC_20231215
(A VARCHAR2(20),
 B NUMBER(20),
 C VARCHAR2(20));
 COMMIT;
 
select * from ABC_20231215 for update;

SELECT A.A,SUM(A.B) AS B, 
MAX(CASE WHEN A.C = A.L THEN A.C ELSE '1' END) AS C 
FROM 
(SELECT A.*, 
LAG(A.C,1)OVER(PARTITION BY A.A ORDER BY A.B) L 
FROM ABC_20231215 A) A 
GROUP BY A.A;

 

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

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

考点：连续记录问题

--创建临时表
DROP TABLE Logs_20231215;
create table Logs_20231215
(ID NUMBER(20),
 NUM NUMBER(20));
 COMMIT;

select * from Logs_20231215 for update;

SELECT A.NUM,
SUM(CASE WHEN A.NUM = A.L THEN 1 ELSE 0 END)+1 AS TIMES  
FROM 
(SELECT A.NUM,LAG(A.NUM, 1) OVER(ORDER BY A.ID) L 
FROM Logs_20231215 A) A 
GROUP BY A.NUM;