Shulou(Shulou.com)06/03 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.