常见的SQL编程题

常见的sql题目

1. 查找不在表里的数据

2. 查找第N高的分数

3. 分组排序

4. 连续出现N类问题

5. 打卡表查某人当月连续最长出勤天数

 

 

1.有两个表A和B，以id关联，查找在A中但是不在B中的数据

方案a：使用left join

select A.id from A left join B on A.id = B.id where B.id is null 

方案b：使用not exists 

select A.id from A where not exists (select 1 from B where A.id = B.id)

方案c：使用not in

select A.id from A where A.id not in(select id from B)

2. 有一个课程分数表，score，cource，id（学生id），查找某门课程第N高的分数或者学生， 如果没有需要返回null值

select ifnull ((select distinct score from A order by score desc limit 1 offset n-1), null) as theNTopScore

3. 分数排名（三种规则  1134 / 1234 / 1123）使用窗口函数

-- 仅根据分数排名
select score, rank() over(partition by course ORDER by score desc) as 'Rank' FROM table;

-- 根据课程分组，分数排名
select score, rank() over(ORDER by score desc) as 'Rank' FROM table;

4. 有N个相同成绩的分数

select score from table group by score having count(*) = N
-- having： 对group by产生的分组进行筛选

 5. 有一个打卡表t_user_sign, 有主键id（自增），有用户id，有打卡时间sign_time

表结构和数据，这里简化一下就直接使用date类型，并且每个人每天最多一条记录

CREATE TABLE t_user_sign (
id int(11) not null AUTO_INCREMENT COMMENT '主键',
user_id varchar(255) DEFAULT NULL COMMENT '用户id',
sign_time date NOT NULL COMMENT '打卡时间',
PRIMARY KEY (id)
);

INSERT INTO t_user_sign (user_id, sign_time) VALUES
('1','2024-03-01'),('1','2024-03-02'),('1','2024-03-03'),('1','2024-03-04'),
('1','2024-03-06'),('1','2024-03-07'),('1','2024-03-08'),('1','2024-03-09'),('1','2024-03-10'),
('1','2024-03-15'),('1','2024-03-16'),('1','2024-03-17'),
('2','2024-03-02'),('2','2024-03-03'),('2','2024-03-04'),
('2','2024-03-06'),
('2','2024-03-15'),('2','2024-03-16'),
('2','2024-03-20');

思路：先对某个人的打卡记录排序得到一个序号，用打卡时间减去序号能得到一个日期，日期相同代表连续打卡，再分组求和

-- 1.使用窗口函数ROW_NUMBER求出这个用户的打卡时间和按时间升序的序号row_num
SELECT sign_time, ROW_NUMBER() over(order by sign_time) as row_num from t_user_sign where user_id='1' and sign_time >= '2024-03-01'

-- 2. 练习使用date_sub函数修改日期
SELECT sign_time, date_sub(sign_time, INTERVAL 2 DAY) as label_date from t_user_sign where user_id='1' and sign_time >= '2024-03-01'

-- 3. 使用date_sub和序号，日期减去序号得到的值label_date，如果label_date相同，则代表连续打卡
SELECT a.sign_time,DATE_SUB(t1.sign_time,  INTERVAL t1.row_num DAY ) as label_date FROM 
(SELECT sign_time, ROW_NUMBER() over(order by sign_time) as row_num from t_user_sign where user_id='1' and sign_time >= '2024-03-01') t1

-- 4. 对label_date进行分组count，代表连续打卡的天数
SELECT t2.label_date, COUNT(t2.label_date) AS days FROM 
( select DATE_SUB(t1.sign_time,  INTERVAL t1.row_num DAY ) as label_date FROM 
(SELECT sign_time, ROW_NUMBER() over(order by sign_time) as row_num from t_user_sign where user_id='1' and sign_time >= '2024-03-01') t1
) t2
GROUP BY t2.label_date

-- 5. 取出最大的一个天数
SELECT t3.days FROM
  (SELECT t2.label_date, COUNT(t2.label_date) AS days FROM 
    (SELECT DATE_SUB(t1.sign_time,  INTERVAL t1.row_num DAY ) as label_date FROM 
      (SELECT sign_time, ROW_NUMBER() over(order by sign_time) as row_num from t_user_sign where user_id='1' and sign_time >= '2024-03-01') t1
    ) t2
  GROUP BY t2.label_date) t3
ORDER BY t3.days desc LIMIT 1