The student numbers of all students whose scores in mathematics are higher than those in Chinese.
The student numbers of all students whose scores in mathematics are higher than those in Chinese.
CREATE TABLE course (
Id int
Sid int
Course string
Score int
)
/ / insert data
/ / Field explanation: id, student number, course, grade
INSERT INTO course VALUES (1,1, 'yuwen', 43)
INSERT INTO course VALUES (2,1, 'shuxue', 55)
INSERT INTO course VALUES (3,2, 'yuwen', 77)
INSERT INTO course VALUES (4,2, 'shuxue', 88)
INSERT INTO course VALUES (5,3, 'yuwen', 98)
INSERT INTO course VALUES (6,3, 'shuxue', 65)
Seek: the student numbers of all students whose scores in mathematics courses are greater than those in Chinese courses.
Select sid,case when course= "yuwen" then score else 0 end as yuwen
Case when course= "shuxue" then score else 0 end as shuxue
From course
1 43 0
1 0 55
2 77 0
2 0 88
3 98 0
3 0 65
Select tmp.sid,Max (tmp.yuwen) as yuwen,max (tmp.shuxue) as shuxue
From (
Select sid,case when course= "yuwen" then score else 0 end as yuwen
Case when course= "shuxue" then score else 0 end as shuxue
From course
) tmp
Group by tmp.sid
1 43 55
2 77 88
3 98 65
Select stmp.sid
From (
Select tmp.sid,Max (tmp.yuwen) as yuwen,max (tmp.shuxue) as shuxue
From (
Select sid,case when course= "yuwen" then score else 0 end as yuwen
Case when course= "shuxue" then score else 0 end as shuxue
From course
) tmp
Group by tmp.sid
) stmp where stmp.shuxue > stmp.yuwen
