Create view statement for Oracle employee Points Ranking

Oracle employee Points Ranking

 

Create View

 

Select t. operate_date,
T. operate_user,
T. operate_dept,
T. add_score +
T. modify_score +
T. over_score +
T. delete_score +
T. up_score +
T. down_score +
T. use_score +
T. opinion_score +
T. advice_score +
T. audit_ OK _score +
T. audit_back_score +
T. read_score

As score
From km_operate_date_log t

 

 

 

Select operate_user, sum (score) from (
Select T. operate_date,
T. operate_user,
T. operate_dept,
T. add_score +
T. modify_score +
T. over_score +
T. delete_score +
T. up_score +
T. down_score +
T. use_score +
T. opinion_score +
T. advice_score +
T. audit_ OK _score +
T. audit_back_score +
T. read_score

As score
From km_operate_date_log t
 
 
) Group by operate_user

 

 

View
Multiple nested select statements affect performance.

Create or replace view km_scoreorder_user
Select operate_user, sum (score) as s_score from (
Select T. operate_date,
T. operate_user,
T. operate_dept,
(T. add_score +
T. modify_score +
T. over_score +
T. delete_score +
T. up_score +
T. down_score +
T. use_score +
T. opinion_score +
T. advice_score +
T. audit_ OK _score +
T. audit_back_score +
T. read_score)

As score
From km_operate_date_log t
)
Group by operate_user;

 

Use a select statement.

Select operate_user, sum (add_score) +
Sum (modify_score) +
Sum (over_score) +
Sum (delete_score) +
Sum (up_score) +
Sum (down_score) +
Sum (use_score) +
Sum (opinion_score) +
Sum (advice_score) +
Sum (audit_ OK _score) +
Sum (audit_back_score) +
Sum (read_score)
As s_score
From km_operate_date_log t
Group by operate_user

 

 

 

Call the created view to retrieve the first few views in SQL.

 

Select row _. *, rownum _ from (
Select * from v_km_operate_sum_score t order by t. sum_score desc
) Row _ where rownum <= 9