Oracle 员工积分排名 创建视图语句

Oracle 员工积分排名

 

建视图

 

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

 

 

视图
嵌套select多，影响性能 一般不用

create or replace view km_scoreorder_user as
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;

 

用一条select语句搞定

 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

 

 

 

调用创建的视图  在SQL中直接取出前几名

 

select row_.*, rownum rownum_ from (
   select * from v_km_operate_sum_score t order by t.sum_score desc
) row_ where rownum <=9