不错sql

--0表示没有问题 1表示有问题
select * from
(select v0.uuid,v0.name, 
               (case when is_operate=1 then (case when v4.oo_score>=60 then (case when v0.oo_cost>0 then 0 else 1 end) else (case when v0.oo_cost=0 then 0 else 1 end) end) else (case when v0.oo_cost=0 then 1 else 0 end) end) oo,
               (case when is_synthetic=1 then (case when v4.gg_score>=60 then (case when v0.gg_cost>0 then 0 else 1 end) else (case when v0.gg_cost=0 then 0 else 1 end) end) else (case when v0.gg_cost=0 then 1 else 0 end) end) gg,
               (case when is_theory=1 then (case when v4.tt_score>=60 then (case when v0.tt_cost>0 then 0 else 1 end) else (case when v0.tt_cost=0 then 0 else 1 end) end) else (case when v0.tt_cost=0 then 1 else 0 end) end) tt,
               (case when is_english=1 then (case when v4.ee_score>=60 then (case when v0.ee_cost>0 then 0 else 1 end) else (case when v0.ee_cost=0 then 0 else 1 end) end) else (case when v0.ee_cost=0 then 1 else 0 end) end) ee
               --decode(v4.oo_score),
               --decode(v4.gg_score),
               --decode(v4.tt_score),
               --decode(v4.ee_score)

from   
  --本次计划的补考考生
  (select f.uuid,f.isdel,f.examinee_type,f.card_number,f.name,f1.work_name,f1.level_code
          ,f.tt_cost,f.oo_cost,f.gg_cost,f.ee_cost,f1.is_theory,f1.is_operate,f1.is_synthetic,f1.is_english
      from nation_examineedetail f,nation_worktype f1
      where f.plan_uuid = 'cbaa6d10-68a6-4ac2-8932-dd87c00f16fd'
      and f.enroll_level_uuid=f1.uuid
      and f.plan_uuid=f1.plan_uuid
       and f.examinee_type = 2
      and f.status >= 210
      and f.status <> 211) v0,
   --补考考生要关联的上次的scoreinfo信息。
   (select         v3.uuid,
               v3.work_name,
               v3.level_name,
               v3.exam_startdate,
               v3.idcard_number,
               v3.name,
               v3.oo_score,
               v3.gg_score,
               v3.tt_score,
               v3.ee_score 
         from (
select v2.*,rank() over (partition by v2.idcard_number,v2.name order by v2.exam_startdate desc) add_rank
  from (select ttt.work_name,
               ttt.level_code,
               tt.exam_startdate,
               t.card_number,
               t.name
          from nation_examineedetail t,
               nation_planinfo       tt,
               nation_worktype       ttt
         where ttt.uuid = t.enroll_level_uuid
           and ttt.plan_uuid = tt.uuid
           and tt.uuid = t.plan_uuid
           and t.plan_uuid = 'cbaa6d10-68a6-4ac2-8932-dd87c00f16fd'
           and t.examinee_type = 2
           and t.status >= 210
           and t.status <> 211) v,
       (select t3.uuid,
               t2.work_name,
               t2.level_name,
               t3.exam_startdate,
               t2.idcard_number,
               t2.name,
               t2.oo_score,
               t2.gg_score,
               t2.tt_score,
               t2.ee_score
          from nation_scoreinfo t2, nation_planinfo t3,nation_examineedetail t4
         where t2.plan_uuid = t3.uuid and t2.examinee_uuid=t4.uuid and t4.plan_uuid=t3.uuid
           and t2.isdel <> 1) v2
 where v.work_name = v2.work_name
   and v.level_code = v2.level_name
   and v.card_number = v2.idcard_number
   and v.name = v2.name
   and v.exam_startdate <> v2.exam_startdate
) v3 where v3.add_rank=1 
) v4
where v4.work_name=v0.work_name
      and      v4.level_name=v0.level_code
      and         v4.idcard_number=v0.card_number
      and         v4.name=v0.name
      )  vv where vv.oo=0 or vv.gg=0 or vv.tt=0 or vv.ee=0
      ;

 

Rank的基本语法为：

RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 

语法：
rank() over (order by 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)

 

1.顺序：asc|desc  名次与业务相关：
  示例：求优秀学员，成绩：降序  迟到次数：升序
2.分区字段：根据什么字段进行分区。

问题：分区与分组有什么区别?
•分区只是将原始数据进行名次排列(记录数不变),
•分组是对原始数据进行聚合统计(记录数变少,每组返回一条)。