You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
43 lines
1.9 KiB
43 lines
1.9 KiB
2 years ago
|
### 一、回写`t_pro_task`的`subject_id`标识
|
||
|
|
```cpp {.line-numbers}
|
||
|
|
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`属性后再写入到数据库
|
||
|
|
|
||
|
|
### 二、查询向挂图作战表中同步的数据
|
||
|
|
```cpp {.line-numbers}
|
||
|
|
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`
|
||
|
|
|
||
|
|
目前来看，只需要这三张表,其它八张表暂可不处理
|