Oracle的一些经典SQL面试题

实例1：测试数据：

create table nba(

team varchar2(20),

year number(4)

)

SQL> select * from nba;

TEAM YEAR

-------------------- -----

活塞 1990

公牛 1991

公牛 1992

公牛 1993

火箭 1994

火箭 1995

公牛 1996

公牛 1997

公牛 1998

马刺 1999

湖人 2000

湖人 2001

湖人 2002

马刺 2003

活塞 2004

马刺 2005

热火 2006

马刺 2007

凯尔特人 2008

湖人 2009

湖人 2010

21 rows selected

请写出一条 SQL 语句，查询出在此期间连续获得冠军的有哪些，其连续的年份的起止时间是多少，结果如下：

TEAM BEGIN END

---------------

公牛 1991 1993

火箭 1994 1995

公牛 1996 1998

湖人 2000 2002

湖人 2009 2010

求解：

SQL> select team, min(t2y), max(t1y)

2 from (select t2.team, t2.year as t2y, t1.year as t1y

3 from nba t1, nba t2

4 where t1.team = t2.team

5 and t1.year = (t2.year + 1)) t

6 group by t.team, (t.t1y - rownum);

TEAM MIN(T2Y) MAX(T1Y)

-------------------- ---------- ----------

公牛 1996 1998

湖人 2009 2010

湖人 2000 2002

公牛 1991 1993

火箭 1994 1995

分析：这个SQL要做的有两件事：

1）求出连续获得冠军的team

2）求出起止年份

关系：根据分析1可知，存在关系team相同的球队(year=year+1),满足这个关系的球队即是连续获得冠军的球队。可以得到SQL如下：

SQL> select t2.team, t2.year as t2y, t1.year as t1y

2 from nba t1, nba t2

3 where t1.team = t2.team

4 and t1.year = (t2.year + 1);

TEAM T2Y T1Y

-------------------- ----- -----

公牛 1991 1992

公牛 1992 1993

火箭 1994 1995

公牛 1996 1997

公牛 1997 1998

湖人 2000 2001

湖人 2001 2002

湖人 2009 2010

8 rows selected

sql中t2y字段表示球队获得冠军每连续两行之间的最小年份，t1y为连续两行记录间的最大年份，最终满足分析1，和2的结果集到此都可以从这个临时表中取得了。

下面我们需要对这个临时表做一个分组，即连续获得冠军的球队我们只需要1行记录用做显示，分组字段是什么呢？观察临时表结构，这里我们采用（team,t1y-rownum）作为分组条件，这样连续获得冠军的球队team就可以用一行显示了。那么我们还差起止年份需要处理一下，就可以出具最终的查询结果了！从临时表结构可以看出通过（team,t1y-rownum）分组后，可以由t2y字段我们求出开始年份，即（min(t2y)），可以由t1y字段求出结止年份，即(max(t1y))！继续处理SQL如下：

SQL> select t.team, min(t2y), max(t1y)

2 from (select t2.team, t2.year as t2y, t1.year as t1y

3 from nba t1, nba t2

4 where t1.team = t2.team

5 and t1.year = (t2.year + 1)) t

6 group by t.team, t1y - rownum

7 order by min(t2y);

TEAM MIN(T2Y) MAX(T1Y)

-------------------- ---------- ----------

公牛 1991 1993

火箭 1994 1995

公牛 1996 1998

湖人 2000 2002

湖人 2009 2010

总结一下，对于这套面试题，对于DBA出身的我，没有处理过类似问题，如果是我遇到了，估计在纸上是写不出来，但是自己处理，是可以求出如上结果的。所以写SQL是一个循序渐进的过程，不是一蹴而就的结果。