T_SQL又另外两种找出３年连续获奖的人

此题目要求查找连续三年获奖的人。首先创建一个表，填充数据。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

;WITH Prize(Name, Year) AS(     SELECT 'Kim', 2001 UNION ALL     SELECT 'Tim', 2002 UNION ALL     SELECT 'Tang', 2002 UNION ALL     SELECT 'Jack', 2001 UNION ALL     SELECT 'Juicy', 2001 UNION ALL     SELECT 'Peff', 2002 UNION All     SELECT 'Juicy', 2002 UNION ALL     SELECT 'Kim', 2002 UNION ALL     SELECT 'Juicy', 2003 UNION ALL     SELECT 'Peff', 2003 UNION ALL     SELECT 'Kim', 2003 UNION ALL     SELECT 'Tim', 2004 UNION ALL     SELECT 'Juicy', 2004 UNION ALL     SELECT 'Jack', 2005 UNION ALL     SELECT 'Eric', 2005 UNION ALL     SELECT 'Tim', 2005 UNION ALL     SELECT 'Eric', 2006 UNION ALL     SELECT 'Peff', 2006 UNION ALL     SELECT 'Juicy', 2007 UNION ALL     SELECT 'Eric', 2007 UNION ALL     SELECT 'Tang', 2007 UNION ALL     SELECT 'Tang', 2008 UNION ALL     SELECT 'Peff', 2008 UNION ALL     SELECT 'Kim', 2008 UNION ALL     SELECT 'Jack', 2009 UNION ALL     SELECT 'Tang', 2009 UNION ALL     SELECT 'Kim', 2009)       SELECT * INTO dbo.Prize FROM Prize ORDER BY Name, Year   CREATE CLUSTERED INDEX cluster_index_main ON dbo.Prize(Name)

Prize表的列Name表示姓名，而year表示获奖的年份。

1 第一种方式：使用T_SQL里面的关键字apply。

Apply有两个输入参数，左边输入和右边输入，右边输入可以为函数，也可以为子查询。对于左边输入的每一行，都会在右输入中进行计算，然后把结果汇总。

那么对于Prize中每一行，我可以把它作为CROSS APPLY的左输入，然后去右输入中查找姓名相等，获奖年份大，但是获奖年份在3年之内的行。如果能找到，

那么结果表中左输入对应行会超过三行。接下来按姓名和月份分组，找出COUNT大于3的就可以了。如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT DISTINCT Name FROM(     SELECT Name, Year, COUNT(LYear) C     FROM(         SELECT *         FROM dbo.Prize P1         CROSS APPLY(             SELECT TOP 3 Year LYear             FROM dbo.Prize P2             WHERE P1.Name = P2.Name AND P2.Year >= P1.Year AND                 P2.Year - P1.Year <=2             ORDER BY Year) AS D1             ) AS d2     GROUP BY Name, Year ) AS D3 WHERE D3.C >=3

 

2 第二种方式，使用CTE递归进行查询。如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

;WITH cte1(name, year, count) AS (     SELECT Name, MIN(year), 1     FROM dbo.Prize     GROUP BY Name              UNION ALL           SELECT p.Name, p.Year ,         CASE             WHEN p.Year = c.year + 1 THEN c.count +1             ElSE 1         END     FROM dbo.Prize p JOIN cte1 c     ON p.Name = c.name AND p.Year > c.year )   SELECT DISTINCT name FROM cte1 WHERE  count >= 3 ORDER BY name

这个递归的定位条件就是按名字分组，找出获奖年最小的那个。接下来就是递归实现的部分。用Prize表和cte表进行连接，连接的条件是名字相等，同时新加入行的获奖年份比

当前大。这种方式的关键是在CASE语句，如果找到的行的year比当前行的year大1，那么就更新count，为当前行的count+1.否则，说明当前行不是连续获奖的year，使count

计数归位，还原为1。最后只需要查找count大于等于3的行。