oracle 提取关键字生成字段排序

    这是今天要解决的表

    CREATE TABLE "NFRC"."TRAIN_COURSE" 
    ( "UNIT_NO" VARCHAR2(40) NOT NULL ENABLE, 
    "NAME" VARCHAR2(200), 
    "COURSE_TYPE1" VARCHAR2(4), 
    "COURSE_TYPE2" VARCHAR2(4), 
    "CLASS_HOUR" VARCHAR2(10), 
    "TEACHER" VARCHAR2(100), 
    "SIGNUP_STOP_DATE" DATE, 
    "START_DATE" DATE, 
    "CALENDAR" VARCHAR2(100), 
    "SCHOOL_LOC" VARCHAR2(6), 
    "TEL" VARCHAR2(100), 
    "TRAIN_COST" VARCHAR2(100), 
    "COURSE_INTR" VARCHAR2(4000), 
    "GOAL" VARCHAR2(4000), 
    "TEACHER_INTR" VARCHAR2(4000), 
    "APPLY_TARGET" VARCHAR2(4000), 
    "BOOKS" VARCHAR2(4000), 
    "DIPLOMA" VARCHAR2(4000), 
    "CLASS_ADDR" VARCHAR2(1000), 
    "USEFUL_LIFE" NUMBER(3,0) NOT NULL ENABLE, 
    "STATE" NUMBER(1,0) NOT NULL ENABLE, 
    "REG_DATE" DATE NOT NULL ENABLE, 
    "REG_OPERATOR" VARCHAR2(18) NOT NULL ENABLE, 
    "VISIT" NUMBER(9,0) DEFAULT 0 NOT NULL ENABLE, 
    "COURSE_NO" VARCHAR2(40) NOT NULL ENABLE, 
    "COURSE" VARCHAR2(6), 
    "PS" VARCHAR2(4000), 
    "L_PRICE" VARCHAR2(100), 
    "COURSE_INTR_MAX" CLOB, 
    "TEACHER_INTR_MAX" CLOB, 
    "GOAL_MAX" CLOB, 
    "BOOKS_MAX" CLOB, 
    "DIPLOMA_MAX" CLOB, 
    "PS_MAX" CLOB, 
    "APPLY_TARGET_MAX" CLOB, 
    CONSTRAINT "IDX_PK_TRAIN_COURSE_NO" PRIMARY KEY ("COURSE_NO"))

     

     

     


    主要问题是在与“NAME”字段里面的的数据都是类似于“四级职业指导师——金牌班”,"x级xxxx师——XX班"这样的,产品部居然是要要按先是级数,然后是xx班排序...........
    问题是根本没有这样的字段,全部都在“NAME”字段里面。(这是原来的表结构的缘故)
    怎么办呢?突然想到可以用oracle的 instr() 和case when 来解决,看下面:

     

    select   1 AS course_type , 
    c.unit_no,c.name,c.course_no,c.class_hour,c.train_cost,
    c.course_intr,c.teacher,c.teacher_intr,c.l_price,
    to_char(c.start_date,'YYYY-MM-DD') start_date,t.name tname,u.name uname,l.name lname ,
     CASE  WHEN instr(c.name,'四级')>0 THEN 4 WHEN instr(c.name,'三级')>0 THEN 3 WHEN  instr(c.NAME,'二级')>0 THEN 2 WHEN  instr(c.NAME,'一级') >0 THEN 1 ELSE 0 END leve,
    CASE  WHEN instr(c.name,'金牌班')>0 THEN 4 WHEN instr(c.name,'签约班')>0 THEN 3 WHEN  instr(c.NAME,'面授班')>0 THEN 2 WHEN  instr(c.NAME,'网授班') >0 THEN 1 ELSE 0 END course_vip from TRAIN_COURSE c,dic_course t,TRAIN_ORGAN u, TRAIN_ORGAN_FEE f, 
    dic_region l where u.unit_no = f.unit_no and f.EXPRI_DATE >= trunc(sysdate) and c.state=2  
    and c.course=t.code(+)  and c.unit_no=u.unit_no(+) and c.school_loc=l.code(+) order by leve DESC ,course_vip DESC , start_date asc, c.REG_DATE desc
    
    



    这样就解决啦

    排序效果

    文章仅在黑客派和本网站刊登,其他为盗载。