用一条sql语句查找记录中的连续数据
背景
oracle作业有一道题:
用一条语句查出连续获得冠军的队伍,和其连续获胜的开始年B、结束年E
数据如下(表名NBA):
TEAM | Y |
---|---|
活塞 | 1990 |
公牛 | 1991 |
公牛 | 1992 |
公牛 | 1993 |
火箭 | 1994 |
火箭 | 1995 |
公牛 | 1996 |
公牛 | 1997 |
公牛 | 1998 |
马刺 | 1999 |
湖人 | 2000 |
湖人 | 2001 |
湖人 | 2002 |
马刺 | 2003 |
活塞 | 2004 |
马刺 | 2005 |
热火 | 2006 |
马刺 | 2007 |
凯尔特人 | 2008 |
湖人 | 2009 |
湖人 | 20010 |
正确的结果集应该是酱紫的:
TEAM | B | E |
---|---|---|
公牛 | 1991 | 1993 |
火箭 | 1994 | 1995 |
公牛 | 1996 | 1998 |
湖人 | 2000 | 2002 |
湖人 | 2009 | 2010 |
思路
既然要求一条语句,就不能用PL/SQL了
考虑先把TEAM和连续获胜的Y选出来,至于B和E,MIN和MAX就能选出来。
那么问题来了,怎么把TEAM和连续获胜的Y选出来
由于oracle内置表中有scott.emp这张表,就用这张表试刀,省得再创建一张表了。
以下sql语句的作用是选出scott.emp表中,同一个deptno,间隔为1150的连续的sal。
SELECT *
FROM scott.emp my_table2
-- 遍历scott.emp每一条记录,从中挑出满足以下条件的记录
WHERE exists(
-- 每个部门分一个组,若该部门内有my_table2.sal + 1150,说明my_table2.sal再间隔1150即有一条满足的记录
-- 因此my_table2.sal所在的记录要被选入
SELECT my_table3.DEPTNO,my_table3.sal
FROM scott.emp my_table3
GROUP BY my_table3.DEPTNO,my_table3.sal
HAVING my_table3.sal = my_table2.sal + 1150 AND my_table3.DEPTNO = my_table2.DEPTNO
)
-- 扫描结束,每个部门的最后一条合法sal都没有被纳入,因此加个union
UNION
SELECT *
FROM SCOTT.EMP
WHERE SCOTT.EMP.SAL IN (
-- 挑出每一个部门里被遗漏最后一个的合法sal
-- 其实就是上面select的结果每一组的最大值+1150,所以from和where是一模一样的
SELECT max(SAL) + 1150
FROM scott.emp my_table2
WHERE exists(
SELECT my_table3.DEPTNO,my_table3.sal
FROM scott.emp my_table3
GROUP BY my_table3.DEPTNO,my_table3.sal
HAVING my_table3.sal = my_table2.sal + 1150 AND my_table3.DEPTNO = my_table2.DEPTNO
)
GROUP BY my_table2.DEPTNO
)
结果集如图
解决办法
上面已经把最重要的resolve了。回到开始的题目,使用以下sql语句应该就能搞定:
SELECT TEAM,min(Y) B,max(Y) E
FROM (
SELECT *
FROM NBA my_table2
WHERE exists(
SELECT my_table3.TEAM,my_table3.Y
FROM NBA my_table3
GROUP BY my_table3.TEAM,my_table3.Y
HAVING my_table3.Y = my_table2.Y + 1 AND my_table3.TEAM = my_table2.TEAM
)
UNION
SELECT *
FROM NBA
WHERE NBA.Y IN (
SELECT max(Y) + 1
FROM NBA my_table2
WHERE exists(
SELECT my_table3.TEAM,my_table3.Y
FROM NBA my_table3
GROUP BY my_table3.TEAM,my_table3.Y
HAVING my_table3.Y = my_table2.Y + 1 AND my_table3.TEAM = my_table2.TEAM
)
GROUP BY my_table2.TEAM
)
) my_table1
GROUP BY TEAM
吐槽
目前为止遇到过最烧脑的一个sql,想了一天,智商需要充值。