项目中用到的SQL语句

select sum(t3.f_person_num) NPERNUM,t2.f_channel_did,t2.f_channel_cid from winBank.t_recruitplan t1,winBank.t_recruitplan_relation t2,winBank.t_recruitplan_insurance t3
              where t1.f_date=2007 and t1.f_plan_type = 1 and t2.f_rec_relation_id = t1.f_id and t3.f_rel_relation_id = t2.f_id
              group by t2.f_channel_cid,t2.f_channel_did order by t2.f_channel_cid,t2.f_channel_did DESC;

 

 

 

private static final String       SELECT_THREEMONTHPLAN__TOTAL_NUM          = "SELECT SUM(T3.F_PERSON_NUM) SUMPER,T2.F_CHANNEL_CID,T2.F_CHANNEL_DID FROM winBank.T_RECRUITPLAN T1,winBank.T_RECRUITPLAN_RELATION T2 ,T_RECRUITPLAN_INSURANCE T3 "
    +" WHERE 1=1 AND T1.F_DATE >? AND T1.F_DATE <? T1.F_PLAN_TYPE="+ConstRecPlanEntity.PLAN_TYPE_THREEMOTH+" AND T2.F_PLAN_TYPE = "+ConstRecPlanEntity.PLAN_TYPE_THREEMOTH
    +" AND T2.F_REC_RELATION_ID = T1.F_ID "
    +" AND T2.F_YEAR_MOTH=TO_DATE(SUBSTR(T1.F_DATE,0,4)||'-'||SUBSTR(T1.F_DATE,5,6)||'-1','YYYY-MM-DD')"
    +" AND T3.F_REL_RELATION_ID = T2.F_ID"
    +" GROUP BY T2.F_CHANNEL_CID,T2.F_CHANNEL_DID";

 

满足条件的更新与查询

update t_recruitplan_insurance t set t.f_person_num = 0 where
t.f_rel_relation_id in(select t.f_id from wintalent3.t_recruitplan_relation t where t.f_channel_did like '48481597%' and t.f_plan_type=1);

 

select t.f_id from wintalent3.t_recruitplan_relation t where t.f_channel_did like '48481597%' and t.f_plan_type=1;

select * from  t_recruitplan_insurance t where
t.f_rel_relation_id in(select t.f_id from wintalent3.t_recruitplan_relation t where t.f_channel_did like '48481597%' and t.f_plan_type=1);