111111

最新推荐文章于 2025-05-27 23:42:29 发布

weixin_44364949

最新推荐文章于 2025-05-27 23:42:29 发布

阅读量66

点赞数

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/weixin_44364949/article/details/126370097

版权

此查询从多个表格联接获取2022年7月14日至15日期间生产线过程数据，重点关注重要部件的完成时间、数量、产品编码、机械编号、工序编码、部件编码等信息。排除特定里程碑类型并忽略已历史标记的数据，同时包含工位ID的工人信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT C1.product_cd,I1.finish_datetime,C1.mecha_no,serial_no,A1.team_cd,A1.process_cd,H1.parts_cd,H1.parts_name_chn,count(h1.parts_cd) as QTY,I1.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
(SELECT physical_line_cd,process_cd,process_version_no,production_date,mecha_no,shift_type_cd,charging_frequency,milestone_cd,working_frequency,milestone_version_no
FROM T_milestone_result WHERE production_date BETWEEN ‘2022-07-14’ AND ‘2022-07-15’) 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
(SELECT finish_datetime,parts_lot,mecha_no as ‘LotNo’,remark,parts_cd,milestone_cd,working_frequency,position_cd,charging_frequency
FROM T_important_parts_result WHERE finish_datetime BETWEEN ‘2022-07-14’
AND ‘2022-07-15’ AND is_history = ‘False’ ) I1 ON i1.LotNo = B1.mecha_no AND i1.working_frequency = b1.working_frequency

INNER JOIN
(SELECT product_cd,mecha_no,serial_no FROM T_mecha WHERE finish_datetime BETWEEN ‘2022-07-14’ AND ‘2022-07-15’)C1 ON b1.mecha_no = c1.mecha_no

INNER JOIN M_important_parts H1 ON i1.parts_cd = h1.parts_cd

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 AND g1.parts_cd = i1.parts_cd and g1.position_cd = i1.position_cd
and i1.charging_frequency = b1.charging_frequency

left JOIN M_append_goods j1 on j1.append_goods_cd = h1.parts_cd

WHERE A1.production_date BETWEEN ‘2022-07-14’ AND ‘2022-07-15’ AND i1.milestone_cd not in (‘MSREPAIR’,‘MSMODIFY’,‘MSREWORK’) and j1.append_goods_cd is null

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.LotNo,I1.remark,B1.milestone_cd,B1.working_frequency,B1.charging_frequency