一道SQL題

有A,B,C,D四個人,他們在某三個測試(C1,C2,C3)上有下面的成績.

如何從下表中找出C2分數超過C1分數的人?

name	subject	score
A	C1	10
A	C2	20
A	C3	20
B	C1	30
B	C2	20
B	C3	20
C	C1	100
C	C2	80
C	C3	20
D	C1	100
D	C2	180
D	C3	20

SQL代碼:

create table tempdb..jerval(
name varchar(20) null,
subject varchar(20) null,
score int null
)
go
insert into tempdb..jerval values('A','C1',10)
insert into tempdb..jerval values('A','C2',20)
insert into tempdb..jerval values('A','C3',20)
insert into tempdb..jerval values('B','C1',30)
insert into tempdb..jerval values('B','C2',20)
insert into tempdb..jerval values('B','C3',20)
insert into tempdb..jerval values('C','C1',100)
insert into tempdb..jerval values('C','C2',80)
insert into tempdb..jerval values('C','C3',20)
insert into tempdb..jerval values('D','C1',100)
insert into tempdb..jerval values('D','C2',180)
insert into tempdb..jerval values('D','C3',20)
go
select t1.name from tempdb..jerval t1 inner join tempdb..jerval t2 on t1.name=t2.name
where t1.subject ='C1' and t2.subject ='C2' and t2.score>t1.score
           

結果:

name

A

D