QingLong/Doc/挂图作战/挂图作战同步数据分析.md

一、回写t_pro_task的subject_id标识

update t_pro_task as t1  set t1.subject_id=t2.subject_id from t_gtzz_subject as t2 where t1.sn=t2.task_sn

根据t_gtzz_subject表中已存在的 task_sn字段进行修改t_pro_task的sn字段，标识已任务已进入挂图作战的任务表。

优化办法:

• 将t_gtzz_subject中所有项目预加载到HashMap,以task_sn为key,value为subject_id
• 在 读入t_pro_task时，逐条修改subject_id属性后再写入到数据库

二、查询向挂图作战表中同步的数据

select t1.sn,
(select itemvalue20 from t_pro_form_value where task_sn=t1.sn limit 1) as money,
(select itemvalue11 from t_pro_form_value where task_sn=t1.sn limit 1) as xmjj,
(select class_name from t_pro_detail  where task_sn=t1.sn order by class_name limit 1 ) as subject_type_name,

t1.subject_id,t1.task_number,t1.task_title,t1.task_year,t1.ex_send_org_name,
t1.create_time from t_pro_task as t1    

left join t_pro_form_value as t2 on t2.task_sn=t1.sn  where   t1.ex_send_org_name in 

(select org_name from t_gtzz_school)  and t1.use_flag=1 and t1.draft_flag=0 and t1.close_flag=1 and  t1.data_type=0  order by t1.create_time desc

从t_pro_form_value中根据task_sn提供

• itemvalue20 -> money
• itemvalue11 -> xmjj

从t_pro_detail中根据task_sn提供class_name,parent_name

从t_pro_task中查找所有ex_send_org_name在t_gtzz_school的org_name中的数据，可以采用预加载t_gtzz_school的办法，用一个set,保留org_name

查询t_pro_task表时，只要 use_flag=1 and draft_flag=0 and close_flag=1 and data_type=0

三、总结

• t_pro_task不需要那么多条，根据sn唯一性，只保留一条即可
• t_pro_form_value
• t_pro_detail

目前来看，只需要这三张表,其它八张表暂可不处理