求一个不同人成绩对应不同成绩等级的查询

DECLARE @Personnel TABLE
    (
        eid INT,   --人员的id
        score INT, --获得的分数
        did INT    --对应的班级id
    )
DECLARE @Grade TABLE
    (
        id INT,           --班级id
        score INT,        --分数
        title VARCHAR(20) --等级名称
    )


INSERT @Personnel( eid, score, did )
VALUES
( 1, 90, 1 ),
( 2, 80, 1 ),
( 3, 72, 1 ),
( 4, 90, 2 ),
( 5, 80, 2 ),
( 6, 72, 2 ),
( 7, 98, 1 )

INSERT @Grade( id, score, title )
VALUES
( 1, 90, '优秀' ),
( 1, 80, '良好' ),
( 1, 70, '中等' ),
( 1, 60, '及格' ),
( 2, 85, '良好' ),
( 2, 60, '及格' ),
( 1, 95, '优秀+' )

DECLARE @Grade加区间 TABLE( id INT, title VARCHAR(20), min_score INT, max_score INT );

WITH te
AS
    (
    SELECT ROW_NUMBER() OVER ( PARTITION BY id ORDER BY score ) 顺序, * FROM @Grade AS A --ORDER BY id
    )
INSERT @Grade加区间( id, title, min_score, max_score )
    SELECT t.id, t.title, t.score, ISNULL(( SELECT TOP 1 te.score FROM te WHERE te.顺序 = t.顺序 + 1 ), 100) AS max_score
        FROM te t

SELECT * FROM @Grade加区间


SELECT *,
       ISNULL(( SELECT TOP 1 G.title FROM @Grade加区间 AS G WHERE P.score >= G.min_score AND P.score < G.max_score ), '其他') 等级
    FROM @Personnel AS P

[此贴子已经被作者于2020-7-26 01:59编辑过]