优化结果3

select xx.product_cd, xx.finish_datetime, xx.mecha_no, xx.serial_no, xx.team_cd, xx.process_cd, xx.parts_cd,xx.parts_name_chn, xx.QTY, xx.LotNo, xx.result, xx.remark, xx.JigNo, xx.worker_id from ( select distinct c1.product_cd, i1.finish_datetime, c1.mecha_no, c1.serial_no, a1.team_cd, a1.process_cd, h1.parts_cd, h1.parts_name_chn, count(h1.parts_cd) as QTY, i1.parts_lot as ‘LotNo’, ‘’ as ‘result’, i1.remark, ‘’ as ‘JigNo’, (select top 1 worker_id from T_worker_result as f where c1.mecha_no = f.mecha_no and b1.milestone_cd = f.milestone_cd and b1.working_frequency = f.working_frequency and b1.charging_frequency = f.charging_frequency) as worker_id from T_line_process_daily_plan a1 INNER JOIN T_milestone_result b1 on a1.physical_line_cd = b1.physical_line_cd and a1.shift_type_cd = b1.shift_type_cd and a1.process_cd = b1.process_cd and a1.process_version_no = b1.process_version_no and a1.production_date = b1.production_date INNER JOIN T_mecha c1 on b1.mecha_no = c1.mecha_no INNER JOIN M_milestone_process d1 on b1.milestone_cd = d1.milestone_cd and b1.milestone_version_no = d1.milestone_version_no and b1.physical_line_cd = d1.physical_line_cd and b1.process_cd = d1.process_cd and b1.process_version_no = d1.process_version_no INNER JOIN M_operation_milestone e1 on d1.milestone_cd = e1.milestone_cd and d1.milestone_version_no = e1.milestone_version_no and d1.product_cd = e1.product_cd INNER JOIN M_small_class f1 on e1.product_cd = f1.product_cd and e1.large_class_cd = f1.large_class_cd and e1.middle_class_cd = f1.middle_class_cd and e1.small_class_cd = f1.small_class_cd and e1.operation_version_no = f1.operation_version_no INNER JOIN M_operation_important_parts g1 on f1.product_cd = g1.product_cd and f1.large_class_cd = g1.large_class_cd and f1.middle_class_cd = g1.middle_class_cd and f1.small_class_cd = g1.small_class_cd and f1.operation_version_no = g1.operation_version_no INNER JOIN T_important_parts_result i1 on b1.mecha_no = i1.mecha_no and g1.parts_cd = i1.parts_cd and g1.position_cd = i1.position_cd and i1.working_frequency = b1.working_frequency and i1.charging_frequency = b1.charging_frequency INNER JOIN M_important_parts h1 on i1.parts_cd = h1.parts_cd where i1.finish_datetime is not null and i1.milestone_cd not in (‘MSREPAIR’,‘MSMODIFY’,‘MSREWORK’) and i1.is_history = ‘False’ and c1.finish_datetime >= convert(datetime, ‘2022-08-15 10:22’) and c1.finish_datetime < convert(datetime,‘2022-08-15 10:22’) and h1.parts_cd not in (select j1.append_goods_cd from M_append_goods as j1 ) group by c1.product_cd, i1.finish_datetime, c1.mecha_no, c1.serial_no ,a1.team_cd, a1.process_cd, h1.parts_cd, h1.parts_name_chn, i1.parts_lot ,i1.remark,worker_idunion all select distinct c1.product_cd, i1.finish_datetime, c1.mecha_no, c1.serial_no, a1.team_cd, a1.process_cd, h1.measure_cd as parts_cd, h1.measure_name_chn as parts_name_chn, count(h1.measure_cd) as QTY, convert(varchar,i1.measure_value) as LotNo, convert(varchar, case i1.is_judge when ‘True’ then ‘OK’ when ‘False’ then ‘NG’ else ‘’ end) as result, i1.remark, ‘’ as JigNo, (select top 1 worker_id from T_worker_result as f where c1.mecha_no = f.mecha_no and b1.milestone_cd = f.milestone_cd and b1.working_frequency = f.working_frequency and b1.charging_frequency = f.charging_frequency) as worker_id from T_line_process_daily_plan a1 INNER JOIN T_milestone_result b1 on a1.physical_line_cd = b1.physical_line_cd and a1.shift_type_cd = b1.shift_type_cd and a1.process_cd = b1.process_cd and a1.process_version_no = b1.process_version_no and a1.production_date = b1.production_date INNER JOIN T_mecha c1 on b1.mecha_no = c1.mecha_no INNER JOIN M_milestone_process d1 on b1.milestone_cd = d1.milestone_cd and b1.milestone_version_no = d1.milestone_version_no and b1.physical_line_cd = d1.physical_line_cd and b1.process_cd = d1.process_cd and b1.process_version_no = d1.process_version_no INNER JOIN M_operation_milestone e1 on d1.milestone_cd = e1.milestone_cd and d1.milestone_version_no = e1.milestone_version_no and d1.product_cd = e1.product_cd INNER JOIN M_small_class f1 on e1.product_cd = f1.product_cd and e1.large_class_cd = f1.large_class_cd and e1.middle_class_cd = f1.middle_class_cd and e1.small_class_cd = f1.small_class_cd and e1.operation_version_no = f1.operation_version_no INNER JOIN M_operation_important_parts g1 on f1.product_cd = g1.product_cd and f1.large_class_cd = g1.large_class_cd and f1.middle_class_cd = g1.middle_class_cd and f1.small_class_cd = g1.small_class_cd and f1.operation_version_no = g1.operation_version_no INNER JOIN T_important_parts_result i1 on b1.mecha_no = i1.mecha_no and g1.parts_cd = i1.parts_cd and g1.position_cd = i1.position_cd and i1.working_frequency = b1.working_frequency and i1.charging_frequency = b1.charging_frequency INNER JOIN M_important_parts h1 on i1.parts_cd = h1.parts_cd where i1.finish_datetime is not null and i1.milestone_cd not in (‘MSREPAIR’,‘MSMODIFY’,‘MSREWORK’) and i1.is_history = ‘False’ and c1.finish_datetime >= convert(datetime, ‘2022-08-15 10:22’) and c1.finish_datetime < convert(datetime,‘2022-08-15 10:22’) group by c1.product_cd, i1.finish_datetime, c1.mecha_no, c1.serial_no ,a1.team_cd, a1.process_cd, h1.parts_cd, h1.parts_name_chn, i1.parts_lot ,i1.remark,worker_idunion all select distinct c1.product_cd, i1.finish_datetime, c1.mecha_no, c1.serial_no, a1.team_cd, a1.process_cd, h1.append_goods_cd as parts_cd, h1.append_goods_name_chn as parts_name_chn, count(h1.append_goods_cd) as QTY, isnull((select j1.parts_lot from T_important_parts_result as j1 where j1.mecha_no = i1.mecha_no and j1.parts_cd = i1.append_goods_cd), case when i1.append_goods_cd is null then null else ‘Ok’ end) as LotNo, ‘’, ‘’ as remark, ‘’ as ‘JigNo’, (select top 1 worker_id from T_worker_result as f where c1.mecha_no = f.mecha_no and b1.milestone_cd = f.milestone_cd and b1.working_frequency = f.working_frequency and b1.charging_frequency = f.charging_frequency) as worker_id from T_line_process_daily_plan a1 INNER JOIN T_milestone_result b1 on a1.physical_line_cd = b1.physical_line_cd and a1.shift_type_cd = b1.shift_type_cd and a1.process_cd = b1.process_cd and a1.process_version_no = b1.process_version_no and a1.production_date = b1.production_date INNER JOIN T_mecha c1 on b1.mecha_no = c1.mecha_no INNER JOIN M_milestone_process d1 on b1.milestone_cd = d1.milestone_cd and b1.milestone_version_no = d1.milestone_version_no and b1.physical_line_cd = d1.physical_line_cd and b1.process_cd = d1.process_cd and b1.process_version_no = d1.process_version_no INNER JOIN M_operation_milestone e1 on d1.milestone_cd = e1.milestone_cd and d1.milestone_version_no = e1.milestone_version_no and d1.product_cd = e1.product_cd INNER JOIN M_small_class f1 on e1.product_cd = f1.product_cd and e1.large_class_cd = f1.large_class_cd and e1.middle_class_cd = f1.middle_class_cd and e1.small_class_cd = f1.small_class_cd and e1.operation_version_no = f1.operation_version_no INNER JOIN M_operation_important_parts g1 on f1.product_cd = g1.product_cd and f1.large_class_cd = g1.large_class_cd and f1.middle_class_cd = g1.middle_class_cd and f1.small_class_cd = g1.small_class_cd and f1.operation_version_no = g1.operation_version_no INNER JOIN T_important_parts_result i1 on b1.mecha_no = i1.mecha_no and g1.parts_cd = i1.parts_cd and g1.position_cd = i1.position_cd and i1.working_frequency = b1.working_frequency and i1.charging_frequency = b1.charging_frequency INNER JOIN M_important_parts h1 on i1.parts_cd = h1.parts_cd where i1.finish_datetime is not null and i1.milestone_cd not in (‘MSREPAIR’,‘MSMODIFY’,‘MSREWORK’) and i1.is_history = ‘False’ and c1.finish_datetime >= convert(datetime, ‘2022-08-15 10:22’) and c1.finish_datetime < convert(datetime,‘2022-08-15 10:22’) group by c1.product_cd, i1.finish_datetime, c1.mecha_no, c1.serial_no ,a1.team_cd, a1.process_cd, h1.parts_cd, h1.parts_name_chn, i1.parts_lot ,i1.remark,worker_id) as xxorder by xx.finish_datetimeOFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY