场景:父表有citycode字段,但其内容来自于子表,子表的数据量不确定,有可能为空,也有可能有N多行,鉴于系统已经运行有一定时间了,产生了一定的数据,需要将父表不同ID下的子表内容写入,用下列代码搞定:
@Override protected void process(JobExecutionContext context) { //轮询将子表city插入至父表 String s = "select s.id,s.citycode from s left join se on(s.id = se.sid) WHERE se.citycode is not null"; String se = "select DISTINCT sid,citycode from se where sid = ? "; List<Record> l_s = Db.use(zz.DS_MOS).find(s);//父表list for (Record r : l_s) { //从父表list轮询 Integer sid = r.getInt("id"); List<Record> l_se = Db.use(zz.DS_MOS).find(se, sid);//查出对应子表内容 if (!xx.isEmpty(l_se)) { for (Record r_se : l_se) {//子表轮询 String citycode = r_se.getStr("citycode"); String oc = r.getStr("operation_citycode"); if (l_se.size() == 1) { r.set("operation_citycode", citycode);//子表只有唯一值时,父=子 } // StringBuilder sb = new StringBuilder(r.getStr("operation_citycode")); if (l_se.size() > 1) {//子表有多条时 if (xx.isEmpty(oc)) { r.set("operation_citycode", citycode + ",");//如父citycode为空,父=子 } else { r.set("operation_citycode", (r.getStr("operation_citycode") + citycode + ","));//否则父=子citycode + "," } } } Db.use(zz.DS_MOS).update("s", r); } } }