Clever Use of the Oracle analysis function row_number ()

Winmenaruto (Pangpang) Level 1: Post completion rate:97.96%

Published on: 14:03:02 The student table has three columns: name, course, and score. Name curricula mark Zhang San Language 70 Li Si math 80 Dynasty English 59 Manzhe 70 Dynasty Chinese 90 ........... What I want to achieve is to list the names of passing subjects: Chinese chemistry and mathematics Zhang San, Li Si Dynasty How can this problem be solved? There are more than three subjects, and there may be an issue. I will skip it later.

 

 

SQL code

  -- Test data: Name curricula mark Zhang San Language 70 Li Si mathematics 80 Dynasty English 59 City South Ma Zhe 70 Dynasty Chinese 90 -- SQL: Select Sn, max (case when curricula = 'China' then name end) language, max (case when curricula = 'mate' then name end) mathematics, max (case when curricula = 'English 'Then name end) English, max (case when curricula = 'mazel' then name end) Ma Zhe from (Select name, curricula, mark, row_number () over (partition by curricula order by name) Sn from t where Mark> 59) group by Sn -- result: Ma Zhe 1 Zhang San Li Si Chengnan 2 Dynasty