Oracle is grouped by pseudo column total scores, and the first three columns of each group are obtai

Adding an extra layer of nested statements solves this problem.

select t.*

from (

select classid,chinese,maths,english, nvl(chinese,0)+ nvl(maths,0)+ nvl(english,0) total

from grade

twhere total < 4

order by total desc

Of course, if you want to get the number number, you can paginate and add another layer to the outside.

Try it. select * from (select t.*,row number() over(partition by classid order by total score desc) total score from

select classid,Chinese,maths,English, nvl(Chinese,0)+ nvl(maths,0)+ nvl(english,0) total score from grade).

t) where the total score is < 4

Wouldn't it be okay to replace English with nvl (Chinese, 0) + nvl (maths, 0) + nvl (english, 0)?

LZ wants to take the top three rankings in the total score, is the classid the same? If it's different, the sorting taken out by partition grouping is all 1, so the sorting will lose its meaning, try it.

select * from (select t.*,row number() over(order by nvl(Chinese,0)+ nvl(maths,0)+ nvl(english,0) desc) total score from grade t) where total score < 4

The logic is very simple, but it is still very clear after reading the analysis.

q2。In Oracle, there is a data table, Exam Result, which contains a record that describes "the results of a certain exam for a certain class and a student.""create table exam result(id number(10) not null, - primary key.

classid number(10) not null, -class id, which is associated with the class table.

userid number(10) not null, -user ID, which is associated with the user table.

examid number(10) not null, - the id of the test paper, which is associated with the test paper table.

result number(3) - achievement.

select * from ( select , row_number() over (partition by order by , desc) rn from exam_result e where in (1,2,3) )where rn <= 3

row number() over is to sort by group, then in descending order. These two fields can of course be different. This function is pretty much the same as nownum, except that:

When using rownum for sorting, the result set is first added to the pseudo-column rownum and then sorted, and this function is to sort first and then calculate the row number after containing the sorting clause

A few more functions:

rank() is the jump order, and when there are two second places, the next place is the fourth place (again within each group).

dense rank()l is a sequential order, with two second places still following third place. In contrast, the row number is a lag(arg1,arg2,arg3) with no duplicate value

arg1 is an expression that is returned from other lines.

arg2 is the offset of the current row partition that you wish to retrieve. is a positive offset when one retrieves the number of previous rows backwards.

arg3 is the value returned when the number represented by arg2 is outside the scope of the group. The method used is the same as row number(). (Reference).

1. select * from (select rownum as rn,name from cus order by name) where rownum <= 3;

The rownum here is regenerated.

2. select * from (select rownum as rn ,name from cus order by name) where rn<= 3;

And the rn here refers to the rownum generated by the sql statement inside, which has been fixed!

After oracle groups the group, extract the first few data of each groupSelect *from (select the field name of the group, row number() over(partition by the field name of the group order by the field name of the sort) as rnfrom table name) where rn < = 10 0 2

Pseudo-columns and pseudo-tables.

The rownumber and rowidrownumber equivalent to the primary key are added in natural order depending on how you sort them.

rowid is not, rowid is the data store is, i.e. generated.

select a.* select classes. Basic information, dense rank() over (partition by class order by grade) rank from

Age report card) a where aName<=3;

select a.*

from ( select class, student number, overall grade,..Additional Information.

dense rank() over(partition by class,student number order by total score desc) rank.

from Grade Grade Report Sheet.

where ..

awhere a.Rank<=3

Note: Ties are allowed, and dense rank() can be replaced with rank() to see the difference.

name is obviously not a pseudo column, it is a column of the name table, and the column name is also called name

In this example, Petenki focuses on the so-called pseudo-matching.

where name = user；

This means that user is the username of the current database login.

1. select * from (select rownum as rn,name from cus order by name) where rownum <= 3;

The Nian Heng rownum here is newly generated by the heavy boy Zhao.

2. select * from (select rownum as rn ,name from cus order by name) where rn<= 3;

And the rn here refers to the rownum generated by the sql statement inside, which is the guess Li that has been fixed!