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 |