SQL（之二）-查询“最长连续登录天数“

0-创建表及插入测试数据

说明：本文主要想分享，如何查询"最长连续登录天数"，具体的测试数据，可以自行添加。

CREATE TABLE USER_LOGINFO(
USER_ID INTEGER ,
LOGIN_DATE DATE,
IS_SIGN_IN VARCHAR2(1) DEFAULT '1'
);
COMMENT ON TABLE USER_LOGINFO IS '用户登录信息表';
COMMENT ON COLUMN USER_LOGINFO.USER_ID IS '用户ID';
COMMENT ON COLUMN USER_LOGINFO.LOGIN_DATE IS '用户登录日期';
COMMENT ON COLUMN USER_LOGINFO.IS_SIGN_IN IS '用户是否签到';
--插入测试数据
INSERT INTO  USER_LOGINFO (USER_ID,LOGIN_DATE)
SELECT A.USER_ID, B.FORMAT  FROM (
SELECT  1 USER_ID FROM DUAL 
UNION ALL 
SELECT  2 USER_ID FROM DUAL 
UNION ALL 
SELECT  3 USER_ID FROM DUAL 
UNION ALL 
SELECT  4USER_ID FROM DUAL 
UNION ALL 
SELECT  5 USER_ID FROM DUAL 
)A,
( 
SELECT TO_DATE('2021-05-17 09:00:00', 'yyyy-mm-dd hh24:mi:ss') AS FORMAT  FROM DUAL
UNION ALL
SELECT TO_DATE('2021-05-17 12:05:00', 'yyyy-mm-dd hh24:mi:ss') AS FORMAT  FROM DUAL 
UNI