The select b.s _name as language result ranking from score a join student b on a.s _id=b.s _id where a.c _id='01' order by a.s _score desc;

The select b.s _name as math scores ranking from score a join student b on a.s _id=b.s _id where a.c _id='02' order by a.s _score desc;

Select b.s _name as English scores ranking from score a join student b on a.s _id=b.s _id where a.c _id='03' order by a.s _score desc;

Each individual SQL statements can be found in all subjects scores ranking, but how to merge into a table? Like this watch

CodePudding user response:

 
Select coalesce (A.r ank_1, B.r ank_2, C.r ank_3) as ranking, language, mathematics, English rank 
The from 
(select dank () over (order by a.s _scord desc) as rank_1, b.s _name as Chinese ranking 
The from score a join student b on a.s _id=b.s _id where a.c _id='01') as a 
Full join 
(select dank () over (order by a.s _scord desc) as rank_2, b.s _name as math rankings 
The from score a join student b on a.s _id=b.s _id where a.c _id='02') as b on A.r ank_1=B.r ank_2 
Full 
(select dank () over (order by a.s _scord desc) as rank_3, b.s _name ranked as English 
The from score a join student b on a.s _id=b.s _id where a.c _id='03') as C on isnull (A.r ank_1, B.r ank_2)=C.r ank_3 

CodePudding user response:

SQL statements written very well ah

CodePudding user response:

A simple cross join example, a table with two columns, one is the id (or ranking), one is content (such as name name)
Select * from TBL t1, TBL t2 where t1. Id=t2. The id is good at joining together above the TBL twice,

Similarly, for each of you query the table, add the ranking list, you can use the rank function, then
Select your name from tbl1 tbl2, tbl3 where tbl1. Id=tbl2. Id and tbl1. Id=tbl3. Id.