【Oracle】关于连续三个月或者连续三天问题

精神绅士。

已于 2023-08-01 16:02:33 修改

阅读量450

点赞数

分类专栏： Oracle 文章标签： oracle 数据库

于 2023-08-01 15:51:10 首次发布

本文链接：https://blog.csdn.net/weixin_45871207/article/details/132043549

版权

Oracle 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

创建demo表

CREATE TABLE DEMO_STUDENT (
T_NAME VARCHAR2(10 CHAR),
T_MON NUMBER,
T_SCORE NUMBER
);
COMMENT ON TABLE DEMO_STUDENT IS '学生每月月考总分表';
COMMENT ON COLUMN DEMO_STUDENT.T_NAME IS '姓名';
COMMENT ON COLUMN DEMO_STUDENT.T_MON IS '月份';
COMMENT ON COLUMN DEMO_STUDENT.T_SCORE IS '分数';

问题：求连续三个月分数大于300的同学

方式一

SELECT T_NAME FROM (
	SELECT T_NAME ,COUNT(A) CON FROM (
		SELECT T_NAME ,T_SCORE ,T_MON,T_MON - ROW_NUMBER OVER(PARTITION BY T_NAME ORDER BY T_MON) A FROM DEMO_STUDENT WHERE T_SCORE > 300 --分数大于300
		)
		GROUP BY T_NAME ,A
	)
	WHERE CON = 3   --连续三个月

方式二

SELECT DISTINCT T_ANME FROM DEMO_STUDENT T1
WHERE T1.T_SCORE > 300
AND T1.T_NAME IN (SELECT T2.T_NAME FROM DEMO_STUDENT T2 WHERE T2.T_SCORE > 300 AND T2.MON = T1.T_MON + 1)
AND T1.T_NAME IN (SELECT T3.T_NAME FROM DEMO_STUDENT T3 WHERE T3.T_SCORE > 300 AND T3.MON = T1.T_MON + 2)