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

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

;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的就可以了。如下：

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递归进行查询。如下：

;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的行。