当我去写一个很复杂的视图，或者数据整理代码的时候，我怎么做？ #311

New Issue

2025-12-30T21:11:17+08:00

bigtran commented

2025-12-30 21:11:17 +08:00

1 按照常规逻辑梳理，把代码的逻辑用“注释”写下来

2 在没有对比，没有参考的情况下，只能把这个视图或者代码生成的数据逐一“肉眼”筛选

2.1 或者把这个生成的数据，放到excel里，按照我们的检验标准，去做一些公式判别，看看是否有异常的情况

3 在有多种方法可以实现这个功能的时候，比如既可以用sql实现，又可以用代码实现，那么把这两个输出结果导成excel，再用excel的公式，以及条件格式来对比两个结果。

举例：
ect_actions 转换为 ect_cycles 表，就是对比的代码生成结果，和sql查询结果

SELECT
	MIN(ect_actions.created_at) as dt,
  left(MIN(ect_actions.created_at),7) as month,
  left(MIN(ect_actions.created_at),4) as quarter,
	MID(MIN(ect_actions.created_at),6,5) as month_day,
  left(MIN(ect_actions.created_at),10) as ymd_day,
	op_batchno,
	CONCAT(op_batchno,LPAD(ect_actions.endoscope_id, 4, '0')) as this_wash_id,
	ect_meta_endoscope.hospital_id as hospital_id,
	ect_meta_endoscope.department_id as department_id,
	ect_actions.endoscope_id,
	ect_meta_endoscope.endoscope_name as endoscope_name,
	ect_meta_endoscope.endoscope_steel_no as endoscope_steel_no,
	ect_meta_endoscope.endoscope_type as endoscope_type,
	ect_meta_endoscope.endoscope_model as endoscope_model,
	null as last_wash_id,
	null as action_id_max,
	null as next_wash_id,
	null as action_id_min,
	ect_actions.opuser_id,
	ect_actions.opuser_name,
	ect_actions.action_type,
	ect_actions.action_type_name,
	ect_actions.op_enhance,
	ect_actions.op_morning,
	null as total_start,
	null as total_end,
	null as total_duration,
	max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`op_starttime` END ) AS `s_qx_start`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`op_endtime` END ) AS `s_qx_end`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`op_duration` END ) AS `s_qx_duration`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_qx_reader_id`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_qx_facility_id`,
	
	max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`op_starttime` END ) AS `s_px_start`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`op_endtime` END ) AS `s_px_end`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`op_duration` END ) AS `s_px_duration`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_px_reader_id`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_px_facility_id`,
	
	max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`op_starttime` END ) AS `s_xd_start`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`op_endtime` END ) AS `s_xd_end`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`op_duration` END ) AS `s_xd_duration`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`reader_id` END ) AS `s_xd_reader_id`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`reader_id` END ) AS `s_xd_facility_id`,
	
	max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`op_starttime` END ) AS `s_zmpx_start`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`op_endtime` END ) AS `s_zmpx_end`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`op_duration` END ) AS `s_zmpx_duration`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_zmpx_reader_id`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_zmpx_facility_id`,
	
	max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`op_starttime` END ) AS `s_gz_start`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`op_endtime` END ) AS `s_gz_end`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`op_duration` END ) AS `s_gz_duration`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`reader_id` END ) AS `s_gz_reader_id`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`reader_id` END ) AS `s_gz_facility_id`,
	
	max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`op_starttime` END ) AS `j_card_jx_start`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`op_endtime` END ) AS `j_card_jx_end`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`op_duration` END ) AS `j_card_jx_duration`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`reader_id` END ) AS `j_reader_id`,
	max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`reader_id` END ) AS `j_facility_id`
	
FROM
	ect_actions left join ect_meta_endoscope on ect_actions.endoscope_id=ect_meta_endoscope.endoscope_id
WHERE
	ect_actions.created_at LIKE "2025-12%" and action_type in (1,2,3,4,5,6) and process_name in ('清洗','漂洗','消毒','消毒浸泡','终末漂洗','末洗','干燥','机洗','结束') and ect_actions.endoscope_id=46
GROUP BY
	ect_actions.endoscope_id,
	ect_actions.op_batchno;

# 1 按照常规逻辑梳理，把代码的逻辑用“注释”写下来 # 2 在没有对比，没有参考的情况下，只能把这个视图或者代码生成的数据逐一“肉眼”筛选 ## 2.1 或者把这个生成的数据，放到excel里，按照我们的检验标准，去做一些公式判别，看看是否有异常的情况 # 3 在有多种方法可以实现这个功能的时候，比如既可以用sql实现，又可以用代码实现，那么把这两个输出结果导成excel，再用excel的公式，以及条件格式来对比两个结果。举例： ect_actions 转换为 ect_cycles 表，就是对比的代码生成结果，和sql查询结果 ``` SELECT MIN(ect_actions.created_at) as dt, left(MIN(ect_actions.created_at),7) as month, left(MIN(ect_actions.created_at),4) as quarter, MID(MIN(ect_actions.created_at),6,5) as month_day, left(MIN(ect_actions.created_at),10) as ymd_day, op_batchno, CONCAT(op_batchno,LPAD(ect_actions.endoscope_id, 4, '0')) as this_wash_id, ect_meta_endoscope.hospital_id as hospital_id, ect_meta_endoscope.department_id as department_id, ect_actions.endoscope_id, ect_meta_endoscope.endoscope_name as endoscope_name, ect_meta_endoscope.endoscope_steel_no as endoscope_steel_no, ect_meta_endoscope.endoscope_type as endoscope_type, ect_meta_endoscope.endoscope_model as endoscope_model, null as last_wash_id, null as action_id_max, null as next_wash_id, null as action_id_min, ect_actions.opuser_id, ect_actions.opuser_name, ect_actions.action_type, ect_actions.action_type_name, ect_actions.op_enhance, ect_actions.op_morning, null as total_start, null as total_end, null as total_duration, max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`op_starttime` END ) AS `s_qx_start`, max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`op_endtime` END ) AS `s_qx_end`, max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`op_duration` END ) AS `s_qx_duration`, max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_qx_reader_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '清洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_qx_facility_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`op_starttime` END ) AS `s_px_start`, max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`op_endtime` END ) AS `s_px_end`, max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`op_duration` END ) AS `s_px_duration`, max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_px_reader_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '漂洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_px_facility_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`op_starttime` END ) AS `s_xd_start`, max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`op_endtime` END ) AS `s_xd_end`, max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`op_duration` END ) AS `s_xd_duration`, max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`reader_id` END ) AS `s_xd_reader_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '消毒' or `ect_actions`.`process_name` = '消毒浸泡' ) THEN `ect_actions`.`reader_id` END ) AS `s_xd_facility_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`op_starttime` END ) AS `s_zmpx_start`, max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`op_endtime` END ) AS `s_zmpx_end`, max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`op_duration` END ) AS `s_zmpx_duration`, max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_zmpx_reader_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '终末漂洗' or `ect_actions`.`process_name` = '末洗' ) THEN `ect_actions`.`reader_id` END ) AS `s_zmpx_facility_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`op_starttime` END ) AS `s_gz_start`, max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`op_endtime` END ) AS `s_gz_end`, max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`op_duration` END ) AS `s_gz_duration`, max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`reader_id` END ) AS `s_gz_reader_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '干燥' ) THEN `ect_actions`.`reader_id` END ) AS `s_gz_facility_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`op_starttime` END ) AS `j_card_jx_start`, max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`op_endtime` END ) AS `j_card_jx_end`, max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`op_duration` END ) AS `j_card_jx_duration`, max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`reader_id` END ) AS `j_reader_id`, max(CASE WHEN ( `ect_actions`.`process_name` = '机洗' ) THEN `ect_actions`.`reader_id` END ) AS `j_facility_id` FROM ect_actions left join ect_meta_endoscope on ect_actions.endoscope_id=ect_meta_endoscope.endoscope_id WHERE ect_actions.created_at LIKE "2025-12%" and action_type in (1,2,3,4,5,6) and process_name in ('清洗','漂洗','消毒','消毒浸泡','终末漂洗','末洗','干燥','机洗','结束') and ect_actions.endoscope_id=46 GROUP BY ect_actions.endoscope_id, ect_actions.op_batchno; ``` ![image.png](/attachments/683f5b16-65b7-4b45-b04b-22490f35b7b6) <img width="800" alt="9f95357bbe9523457a31f64f692b55f0.png" src="attachments/50a12bf3-ff05-458f-9c01-ebc5af15f104">

9f95357bbe9523457a31f64f692b55f0.png

583 KiB

image.png

94 KiB

bigtran added the 4-document label 2025-12-30 21:11:32 +08:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: temp-tickets/tickets#311

当我去写一个很复杂的视图，或者数据整理代码的时候，我怎么做？ #311

1 按照常规逻辑梳理，把代码的逻辑用“注释”写下来

2 在没有对比，没有参考的情况下，只能把这个视图或者代码生成的数据 逐一“肉眼”筛选

2.1 或者把这个生成的数据，放到excel里，按照我们的检验标准，去做一些公式判别，看看是否有异常的情况

3 在有多种方法可以实现这个功能的时候，比如既可以用sql实现，又可以用代码实现，那么把这两个输出结果导成excel，再用excel的公式，以及条件格式来对比两个结果。

2 在没有对比，没有参考的情况下，只能把这个视图或者代码生成的数据逐一“肉眼”筛选