blog,blog,blog

这是今天要解决的表

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

这样就解决啦