Oracle数据查询问题

[shpery]

有如下NBA这张表：

现要求查询出连续夺冠的球队名字，开始的年份和结束的年份，得到的数据应该为下：
NAME STARTYEAR ENDYEAR
湖人 2021 2022
热火 2024 2026
湖人 2028 2029



我的思路是：自连接，取得连续的名字，然后取得连续的min（year），再取得max(year),判断year+1
但是查出来还是有点问题
求SQL语句！！！

[AcHerat]

with tmp as
(
    select a.*,
                rownum() as sid,
                row_number() over (partition by name order by year) as rid
    from nba
)

select name,min(year) as startyear,max(year) as endyear
from tmp
group by name,rid-sid

[hyee]

可以考虑用层级函数，例如

SELECT NAME, MIN(YEAR), MAX(YEAR)
FROM   (SELECT a.*,
               LEVEL lv,
               MAX(LEVEL) OVER(PARTITION BY ROWID) max_lv,
               connect_by_root(YEAR) root_year
        FROM   nba a
        CONNECT BY (YEAR - 1 = PRIOR YEAR AND NAME = PRIOR NAME))
WHERE  lv = max_lv
GROUP  BY NAME, root_year

[shpery]

不好意思 出差没来得及回复

[shpery]

引用 2 楼 hyee 的回复:

可以考虑用层级函数，例如

SELECT NAME, MIN(YEAR), MAX(YEAR)
FROM   (SELECT a.*,
               LEVEL lv,
               MAX(LEVEL) OVER(PARTITION BY ROWID) max_lv,
               connect_by_root(YEAR) root_year
        FROM   nba a
        CONNECT BY (YEAR - 1 = PRIOR YEAR AND NAME = PRIOR NAME))
WHERE  lv = max_lv
GROUP  BY NAME, root_year

查询出来的结果有问题

[shpery]

引用 1 楼 AcHerat 的回复:

with tmp as
(
    select a.*,
                rownum() as sid,
                row_number() over (partition by name order by year) as rid
    from nba
)

select name,min(year) as startyear,max(year) as endyear
from tmp
group by name,rid-sid

执行会报rownum出错

[sunboy_yf]

用oracle 的 lead 或者 lag分析函数测试一下

[ym90483]

最初的想法是想把红框内的记录分为一组，但这需要一个标志位，将year-rank置为标志位，想了许久。。。

WITH a AS (SELECT NAME,YEAR,dense_rank()over(PARTITION BY NAME ORDER BY YEAR) rank FROM nba),
     b AS (SELECT NAME,MIN(YEAR) startyear,MAX(YEAR) ENDYEAR FROM a GROUP BY NAME,YEAR-rank)
SELECT * FROM b WHERE startyear!=endyear

可以再多些记录，然后测试一下

[小灰狼W]

with nba as
 (select rownum id, '湖人' name, 2020 + rownum year
    from dual
  connect by rownum <= 2
  union all
  select 3, '马刺', 2023
    from dual
  union all
  select rownum + 3, '热火', rownum + 2023
    from dual
  connect by rownum <= 3
  union all
  select 7, '火箭', 2027
    from dual
  union all
  select rownum + 7, '湖人', rownum + 2027 from dual connect by rownum <= 2)


select name, min(year), max(year)
  from nba t
 start with not exists (select 1
               from nba
              where name = t.name
                and year = t.year - 1)
connect by prior name = name
       and prior year = year - 1
 group by name, connect_by_root id
having count (1) > 1
 order by 2;

数据量大的话在Year字段上建索引