SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE scroe (
no_read nvarchar(40) NULL ,
subject nvarchar(40) NULL ,
score1 int NULL);

INSERT INTO scroe (no_read,subject,score1)
VALUES 
('1','1',50),
('1','2',60),
('2','1',70),
('2','2',50),
('3','1',40),
('3','2',50),
('4','1',80),
('4','2',60),
('4','3',90),
('5','1',65),
('5','2',75),
('5','3',25);

SELECT *
FROM scroe;


no_read
subject
score1


1
1
50

1
2
60

2
1
70

2
2
50

3
1
40

3
2
50

4
1
80

4
2
60

4
3
90

5
1
65

5
2
75

5
3
25


-- 1.no_read 限定在 ('1','2','3'),subject 限定在 ('1','2')
SELECT *
FROM (
SELECT X.no_read,X.subject,X.score1,
X.score1 -
LEAD(X.score1,1) OVER (PARTITION BY X.no_read ORDER BY X.subject)  AS Result
FROM scroe AS X
WHERE X.no_read IN ('1','2','3') AND X.subject IN ('1','2')
) AS Y
WHERE Y.Result>0
ORDER BY Y.no_read


no_read
subject
score1
Result


2
1
70
20


-- 2.no_read 限定在 ('1','2','3','4','5'),subject 限定在 ('1','2')
SELECT *
FROM (
SELECT X.no_read,X.subject,X.score1,
X.score1 -
LEAD(X.score1,1) OVER (PARTITION BY X.no_read ORDER BY X.subject)  AS Result
FROM scroe AS X
WHERE X.no_read IN ('1','2','3','4','5') AND X.subject IN ('1','2')
) AS Y
WHERE Y.Result>0
ORDER BY Y.no_read


no_read
subject
score1
Result


2
1
70
20

4
1
80
20


-- 3.no_read 限定在 ('1','2','3','4','5'),subject 限定在 ('1','3')
SELECT *
FROM (
SELECT X.no_read,X.subject,X.score1,
X.score1 -
LEAD(X.score1,1) OVER (PARTITION BY X.no_read ORDER BY X.subject)  AS Result
FROM scroe AS X
WHERE X.no_read IN ('1','2','3','4','5') AND X.subject IN ('1','3')
) AS Y
WHERE Y.Result>0
ORDER BY Y.no_read


no_read
subject
score1
Result


5
1
65
40