驾校的模拟考试系统，判别连续3次（变量）考试90分（变量）以上的学员有哪些，连续3次不知道怎么判断，想求助于各位。想通过sql语句直接查询出来 有可能么？用row_number（）有了点靠谱的感觉 但还是差最后一点。
------解决思路----------------------
DECLARE @number INT,@score INT
SELECT @number=3,@score=90
;WITH a1 (id,_name,score) AS
(
SELECT 1,'A',89 UNION ALL
SELECT 2,'B',99 UNION ALL
SELECT 3,'A',91 UNION ALL
SELECT 4,'B',92 UNION ALL
SELECT 5,'A',93 UNION ALL
SELECT 6,'B',94
)
,a2 as
(
SELECT _name,MIN(id) id
FROM a1
WHERE score>=@score
GROUP BY _name
)
SELECT _name
FROM a2 a
CROSS APPLY
(
SELECT SUM(CASE WHEN score>=@score THEN 1 ELSE 0 END) score
FROM (SELECT TOP (@number-1) score FROM a1 WHERE _name=a._name AND id>a.id ORDER BY id) b1
) b
WHERE b.score=@number-1
------解决思路----------------------
调整一下
DECLARE @TIMES INT,@SCORE INT
SET @TIMES=3
SET @SCORE=90
;WITH TB(ID,学员ID,成绩)AS(
SELECT 1,'A',90 UNION ALL
SELECT 2,'B',89 UNION ALL
SELECT 3,'A',89 UNION ALL
SELECT 4,'B',92 UNION ALL
SELECT 5,'B',90 UNION ALL
SELECT 6,'A',89 UNION ALL
SELECT 7,'B',94 UNION ALL
SELECT 8,'A',94 UNION ALL
SELECT 9,'A',94 UNION ALL
SELECT 10,'A',94
)
,CTE1 AS(
SELECT *
,ROW_NUMBER()OVER(PARTITION BY 学员ID ORDER BY ID)RN1
FROM TB
)
,CTE2 AS(
SELECT *
,ROW_NUMBER()OVER(PARTITION BY 学员ID ORDER BY ID)RN2
FROM CTE1
WHERE 成绩>=@SCORE
)
/*--直接查学员
SELECT 学员ID
FROM CTE2
GROUP BY RN1-RN2,学员ID
HAVING COUNT(ID)>=@TIMES
*/
SELECT * FROM(
SELECT ID,学员ID,成绩,COUNT(ID)OVER(PARTITION BY RN1-RN2,学员ID)C
FROM CTE2
)T
WHERE C>=@TIMES