求助判断连续sql排序语句

[陈元龙]

驾校的模拟考试系统，判别连续3次（变量）考试90分（变量）以上的学员有哪些，连续3次不知道怎么判断，想求助于各位。想通过sql语句直接查询出来 有可能么？用row_number（）有了点靠谱的感觉 但还是差最后一点。

[还在加载中灬]

最好有表结构

[lzw_0736]

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

[陈元龙]

引用 2 楼 lzw_0736 的回复:

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

这个有点问题哦 假如是 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 的数据 A就判断不出来 表其实很简单 有用的就是ID 学员ID 成绩

[还在加载中灬]

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(ORDER BY ID)RN1
		--如果你的ID一定是连续的，则不需要RN1，
		--你可以别名ID为RN1，或者把之后的RN1换成ID
	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

你参考一下

[还在加载中灬]

调整一下

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