NBA连冠题&连续登陆题&连续纳税题答案

--NBA连冠题

create table t_nba(

team_name varchar2(100),

champion_year number

);

insert into t_nba (team_name, champion_year) values ('活塞', 1990);

insert into t_nba (team_name, champion_year) values ('公牛', 1991);

insert into t_nba (team_name, champion_year) values ('公牛', 1992);

insert into t_nba (team_name, champion_year) values ('公牛', 1993);

insert into t_nba (team_name, champion_year) values ('火箭', 1994);

insert into t_nba (team_name, champion_year) values ('火箭', 1995);

insert into t_nba (team_name, champion_year) values ('公牛', 1996);

insert into t_nba (team_name, champion_year) values ('公牛', 1997);

insert into t_nba (team_name, champion_year) values ('公牛', 1998);

insert into t_nba (team_name, champion_year) values ('马刺', 1999);

insert into t_nba (team_name, champion_year) values ('湖人', 2000);

insert into t_nba (team_name, champion_year) values ('湖人', 2001);

insert into t_nba (team_name, champion_year) values ('湖人', 2002);

insert into t_nba (team_name, champion_year) values ('马刺', 2003);

insert into t_nba (team_name, champion_year) values ('活塞', 2004);

insert into t_nba (team_name, champion_year) values ('马刺', 2005);

insert into t_nba (team_name, champion_year) values ('热火', 2006);

insert into t_nba (team_name, champion_year) values ('马刺', 2007);

insert into t_nba (team_name, champion_year) values ('凯尔特人', 2008);

insert into t_nba (team_name, champion_year) values ('湖人', 2009);

insert into t_nba (team_name, champion_year) values ('湖人', 2010);

commit;

select * from t_nba;

--请写出一条SQL语句，查询出在此期间连续获得冠军的队伍有哪些，其连续的年份的起止时间是多少？

例如：

team_name begin_date end_date

公牛 1991 1993

火箭 1994 1995

公牛 1996 1998

湖人 2000 2002

湖人 2009 2010

--方法一：

select max(team_name) team_name,

min(champion_year) begin_date,

max(champion_year) end_date

from (select team_name, champion_year

from (select team_name,

champion_year,

lag(champion_year, 1) over(partition by team_name order by champion_year) before, --上一个

lead(champion_year, 1) over(partition by team_name order by champion_year) after --下一个

from t_nba) a

where a.champion_year = a.after - 1

or a.champion_year = a.before + 1) b

group by b.champion_year - rownum

order by begin_date;

--方法二：

with tmp_a as(

select t.team_name,

t.champion_year,

row_number() over(partition by t.team_name order by t.champion_year) rn

from t_nba t)

select t.team_name,

min(t.champion_year) begin_date,

max(t.champion_year) end_date

from tmp_a t

group by t.team_name,t.champion_year - rn

having count(t.champion_year - rn) > 1

order by begin_date;

--连续登陆题

create table t_user_login(

user_id number,

login_date date

);

insert into t_user_login(user_id, login_date) values(1,to_date('20200808','yyyy-mm-dd') - 3);

insert into t_user_login(user_id, login_date) values(1,to_date('20200808','yyyy-mm-dd') - 2);

insert into t_user_login(user_id, login_date) values(1,to_date('20200808','yyyy-mm-dd') - 1);

insert into t_user_login(user_id, login_date) values(1,to_date('20200808','yyyy-mm-dd'));

insert into t_user_login(user_id, login_date) values(2,to_date('20200808','yyyy-mm-dd') - 2);

insert into t_user_login(user_id, login_date) values(2,to_date('20200808','yyyy-mm-dd') - 1);

insert into t_user_login(user_id, login_date) values(2,to_date('20200808','yyyy-mm-dd'));

insert into t_user_login(user_id, login_date) values(3,to_date('20200808','yyyy-mm-dd') - 5);

insert into t_user_login(user_id, login_date) values(3,to_date('20200808','yyyy-mm-dd') - 4);

insert into t_user_login(user_id, login_date) values(3,to_date('20200808','yyyy-mm-dd') - 3);

insert into t_user_login(user_id, login_date) values(3,to_date('20200808','yyyy-mm-dd'));

insert into t_user_login(user_id, login_date) values(4,to_date('20200808','yyyy-mm-dd') - 4);

insert into t_user_login(user_id, login_date) values(4,to_date('20200808','yyyy-mm-dd') - 2);

insert into t_user_login(user_id, login_date) values(4,to_date('20200808','yyyy-mm-dd') - 1);

insert into t_user_login(user_id, login_date) values(4,to_date('20200808','yyyy-mm-dd'));

insert into t_user_login(user_id, login_date) values(5,to_date('20200808','yyyy-mm-dd'));

commit;

select * from t_user_login;

--查询用户最长连续登陆的天数

例如：

user_id series_day start_date end_date

1 4 20200805 20200808

2 3 20200806 20200808

3 3 20200803 20200805

4 3 20200806 20200808

select user_id,

min(login_date) as start_date,

max(login_date) as end_date,

count(1) as series_day

from (select user_id,

login_date,

row_number() over(partition by user_id order by login_date) as sort

from t_user_login) a

group by a.user_id, a.login_date - a.sort

having count(a.login_date - a.sort) > 1

order by a.user_id;

--连续纳税题

create table test(

year number,

ent varchar2(10),

amt number

);

insert into test (year, ent, amt) values(2010,'AA',2000);

insert into test (year, ent, amt) values(2011,'AA',2500);

insert into test (year, ent, amt) values(2012,'AA',2300);

insert into test (year, ent, amt) values(2013,'AA',1900);

insert into test (year, ent, amt) values(2014,'AA',2200);

insert into test (year, ent, amt) values(2015,'AA',2300);

insert into test (year, ent, amt) values(2016,'AA',2006);

insert into test (year, ent, amt) values(2010,'BB',2400);

insert into test (year, ent, amt) values(2011,'BB',2401);

insert into test (year, ent, amt) values(2012,'BB',1800);

insert into test (year, ent, amt) values(2013,'BB',2100);

insert into test (year, ent, amt) values(2014,'BB',2101);

insert into test (year, ent, amt) values(2015,'BB',2102);

insert into test (year, ent, amt) values(2016,'BB',2020);

commit;

查询出在此期间连续3年纳税金额大于等于2000万的企业有哪些，

其连续的年份的起止时间是多少，平均纳税金额是多少，结果如下：

企业名称开始时间结束时间平均纳税

AA 2010 2012 2266.7

AA 2014 2016 2168.7

BB 2013 2016 2080.8

select * from test;

with tmp_a as(

select t.year,

t.ent,

t.amt,

row_number() over(partition by t.ent order by t.year) rn

from test t

where t.amt >= 2000)

select t.ent 企业名称,

count(t.year - rn) 连续时间,

min(t.year) 开始时间,

max(t.year) 结束时间,

round(avg(t.amt),1) 平均纳税

from tmp_a t

group by t.ent,t.year - rn

having count(t.year - rn) > 2

order by 企业名称,开始时间;