【Oracle】查历史表⾥最近七天有多少⼈留下过登录记录有⼀张登录历史表：
create table AccessHistory(
id int,
userid int,
loginDate timestamp,
primary key(id)
);
测试数据如下：
insert into AccessHistory values(1,1,to_date('2021.10.13 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(2,2,to_date('2021.10.12 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(3,3,to_date('2021.10.11 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(4,1,to_date('2021.10.13 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(5,2,to_date('2021.10.10 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(6,3,to_date('2021.10.09 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(7,4,to_date('2021.10.08 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(8,5,to_date('2021.10.10 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(9,3,to_date('2021.10.12 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(10,1,to_date('2021.10.11 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(11,3,to_date('2021.10.06 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(12,4,to_date('2021.10.07 08:00:00','yyyy.MM.dd hh24:mi:ss'));
求七天内的登陆记录不难，sql如下：
select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6) order by vdate
SQL>select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6) order by vdate;
VDATE          USERID
---------- ----------
10.074
10.084
10.093
10.102
10.105
10.111
10.113
10.122
10.123
10.131
10.131
已选择11⾏。
在此基础上，我们可以得出最近七天有多少⼈次登录：
（中间SQL）
select a.vdate,count(*) from
(select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
group by a.vdate order by vdate
SQL>select a.vdate,count(*) from
connect和join的区别2  (select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
3group by a.vdate order by vdate;
VDATE        COUNT(*)
---------- ----------
10.071
10.081
10.091
10.102
10.112
10.122
10.132
已选择7⾏。
select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8
SQL>select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0as vcnt from dual connect by level<8;
VDATE            VCNT
---------- ----------
10.070
10.080
10.090
10.100
10.110
10.120
10.130
已选择7⾏。
然后以连续序列为左表，七天数据为右表，即可呈现完美的登录记录。
select b.vdate,nvl(ct,b.vcnt) from
(select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8) b left join
( select a.vdate,count(*) as cnt from
(select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a group by a.vdate ) c
on b.vdate=c.vdate
order by b.vdate
SQL>select b.vdate,nvl(ct,b.vcnt) from
2  (select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0as vcnt from dual connect by level<8) b
3left join
4  ( select a.vdate,count(*) as cnt from
5  (select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
6group by a.vdate ) c
7on b.vdate=c.vdate
8order by b.vdate;
VDATE      NVL(C.CNT,B.VCNT)
---------- -----------------
10.071
10.081
10.091
10.102
10.112
10.122
10.132
已选择7⾏。
为了验证⽣成连续序列的必要性，我们可以删除表中数据，然后插⼊以下⾏：
insert into AccessHistory values(1,1,to_date('2021.10.13 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(2,2,to_date('2021.10.12 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(3,3,to_date('2021.10.11 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(4,1,to_date('2021.10.13 08:00:00','yyyy.MM.dd hh24:mi:ss'));
现在七天数据不是慢的了，看看执⾏中间sql的效果：
select a.vdate,count(*) from
(select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a group by a.vdate order by vdate
SQL>select a.vdate,count(*) from
2  (select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
3group by a.vdate order by vdate;
VDATE        COUNT(*)
---------- ----------
10.111
10.121
10.132
看吧，只有三天，前四天为零的记录全不在了。
⽽执⾏最终sql：
select b.vdate,nvl(ct,b.vcnt) from
(select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0as vcnt from dual connect by level<8) b
left join
( select a.vdate,count(*) as cnt from
(select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
group by a.vdate ) c
on b.vdate=c.vdate
order by b.vdate
SQL>select b.vdate,nvl(ct,b.vcnt) from
2  (select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0as vcnt from dual connect by level<8) b
3left join
4  ( select a.vdate,count(*) as cnt from
5  (select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
6group by a.vdate ) c
7on b.vdate=c.vdate
8order by b.vdate;
VDATE      NVL(C.CNT,B.VCNT)
---------- -----------------
10.070
10.080
10.090
10.100
10.111
10.121
10.132
已选择7⾏。
七天数据全在，没有遗漏。
有些⼈觉得中间sql就够了，然后将数据拿到服务器端，⽤Java⽣成7天的连续序列，再把数据挨个往⾥填，这样多了⼀次IO，不划算，还是尽量在DB端把数据整理完最好。
END

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。