There is a data table named test. The structure is as follows: locate the first (parallel ranking problem) of each course select * from (select *, ROW_NUMBER () over (partitionbycoursenameorderbymarkdesc)
The existing test data table has the following structure: studentnamecoursenamemark aa80 ab60 ac30 ba40 bb50 bc58 ca60 cb70 cc88 da90 db70 dc88 1. Find out the first name (parallel ranking problem) of each course select * from (select *, ROW_NUMBER () over (partition by coursename order by mark desc)
The existing data table test has the following structure:
Studentnamecoursenamemark
Aa80
Ab60
Ac30
Ba40
Bb50
Bc58
Ca60
Cb70
Cc88
Da90
Db70
Dc88
1. Find out the first place in each course (parallel ranking problem)
Select * from (
Select *, ROW_NUMBER () over (partition by coursename order by mark desc) ranks
From test_a
)
Where a. ranks = 1
Partition by: partition. by coursename, you can get the first place in each course by mark. The Hong Kong virtual host can also be the top three or top five if you change the condition.
2. Find out at least two students whose homework score is 60.
Select a. studentname from (
Select studentname, coursename
From test_a where mark> 60
)
Group by a. studentname
Having count (a. coursename)> = 2
, US space, Hong Kong Space