三表关联查询-多次LEFT JOIN...ON

本文介绍了一段用于查询荣誉记录及其关联图片信息的SQL语句。该查询通过左连接的方式将荣誉记录表与图片表进行关联,并按年份筛选数据,最终依据荣誉记录的排序字段降序排列。

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

$sql = "SELECT a.year,a.guid,a.description,b.description AS pic_des,c.year AS pic_year,c.month AS pic_month,c.day AS pic_day,c.file AS pic_file 
FROM m_honor2_honor2 a LEFT JOIN m_honor2_pics b ON a.guid = b.honor2_guid 
LEFT JOIN sys_file c ON b.image= c.guid
WHERE a.year = $year
ORDER BY a.order_is DESC";

目前执行以下SQL查询效率太低了,帮忙优化下SQL写法: SELECT abc1.a3_code, abc1.a2_code, abc1.a2_name, abc1.a6_code, abc1.login_name, -- 报工人账号 abc1.a6_code, -- 序列号 abc1.ACTUALSTARTTIME,-- 开始时间 abc1.ACTUALENDTIME ,--结束时间 abc2.work_hours -- 工时 FROM ( SELECT a3.code a3_code,-- 工单 a2.code a2_code,-- 工作号 a2.Name a2_name,-- 工作名 a7.login_name, -- 报工人账号 a6.code a6_code, -- 序列号 a1.ACTUALSTARTTIME,-- 开始时间 a1.ACTUALENDTIME --结束时间 FROM F_PRODUCTIONWOMENTRESPONSE6635 a1 --生产工作实绩 LEFT JOIN F_WORKSEGMENTREQUIREMENT a2 -- 工作 ON a1.WORKSEGMENTREQUIREMENT=a2.id LEFT JOIN F_WORKREQUEST a3 --工单 ON a2.WorkRequest=a3.id LEFT JOIN F_MATERIALACTUAL a4 -- 物料实绩 ON a1.id =a4.SOURCE_ID LEFT JOIN F_MATERIALACTUALSN a5 -- 物料实绩的序列号 ON a4.id=a5.SOURCE_ID LEFT JOIN F_INDIVIDUALOBJECT a6 -- 实物对象 ON a5.related_id=a6.ID LEFT JOIN F_USER a7 --用户 ON a1.created_by_id =a7.ID WHERE a1.ACTUALSTARTTIME IS NOT NULL OR a1.ACTUALENDTIME IS NOT NULL ORDER BY a2_code DESC,a6_code ) abc1 LEFT JOIN ( SELECT a3.code AS a3_code, a2.code AS a2_code, a2.Name AS a2_name, a6.code AS a6_code, MIN(a1.ACTUALSTARTTIME) AS earliest_start_time, MAX(a1.ACTUALENDTIME) AS latest_end_time, -- 使用方法1计算 ROUND( (EXTRACT(DAY FROM (MAX(a1.ACTUALENDTIME) - MIN(a1.ACTUALSTARTTIME))) * 24 + EXTRACT(HOUR FROM (MAX(a1.ACTUALENDTIME) - MIN(a1.ACTUALSTARTTIME))) + EXTRACT(MINUTE FROM (MAX(a1.ACTUALENDTIME) - MIN(a1.ACTUALSTARTTIME))) / 60 + EXTRACT(SECOND FROM (MAX(a1.ACTUALENDTIME) - MIN(a1.ACTUALSTARTTIME))) / 3600), 3) AS work_hours FROM F_PRODUCTIONWOMENTRESPONSE6635 a1 LEFT JOIN F_WORKSEGMENTREQUIREMENT a2 ON a1.WORKSEGMENTREQUIREMENT = a2.id LEFT JOIN F_WORKREQUEST a3 ON a2.WorkRequest = a3.id LEFT JOIN F_MATERIALACTUAL a4 ON a1.id = a4.SOURCE_ID LEFT JOIN F_MATERIALACTUALSN a5 ON a4.id = a5.SOURCE_ID LEFT JOIN F_INDIVIDUALOBJECT a6 ON a5.related_id = a6.ID LEFT JOIN F_USER a7 ON a1.created_by_id = a7.ID WHERE a1.ACTUALSTARTTIME IS NOT NULL OR a1.ACTUALENDTIME IS NOT NULL GROUP BY a3.code, a2.code, a2.Name, a7.login_name, a6.code HAVING MIN(a1.ACTUALSTARTTIME) IS NOT NULL AND MAX(a1.ACTUALENDTIME) IS NOT NULL ORDER BY a2_code DESC, a6_code ) abc2 ON abc1.a3_code=abc2.a3_code AND abc1.a2_code=abc2.a2_code AND abc1.a2_name=abc2.a2_name AND abc1.a6_code=abc2.a6_code
03-26
oracle实现:取LOTNO max_measure_time之前的五个不同wwi.LOT_ID作为bsl_lot_id,并要取出bsl_lot_id对应的若干个bsl_wafer_id,如不足五条记录或完全重复数据,则取出不足五条数据即可。应该如何修改这段代码 SELECT measuretime.LOTNO–B004957 ,measuretime.PRODUCT_ID–0668A ,measuretime.PRODUCT_NO --123 ,measuretime.TRIM_STAGE ,measuretime.T_WAFER_ID–B004957-03 ,measuretime.LOT_ID–1708380 ,measuretime.WAFER_ID–6519598 6521374 ,measuretime.WAFER_NO–03 04 ,measuretime.TEST_PROGRAM–0668A_WATY ,measuretime.WAT_PARAMETER_ID --123 ,measuretime.PARAMETER_NAME --VTSAT_855PG_1_D020 ,measuretime.limit_file ,measuretime.max_MEASURE_TIME ,wwi.LOT_ID AS bsl_LOT_ID --1708380 ,wwi.WAFER_ID AS bsl_WAFER_ID–6519598 6521374 ,wwi.WAFER_NO AS bsl_WAFER_NO–03 04 ,wwi.MEASURE_TIME AS bsl_MEASURE_TIME ,ROW_NUMBER() OVER (PARTITION BY measuretime.LOTNO ORDER BY wwi.MEASURE_TIME desc) AS rn FROM ( SELECT wat.LOTNO --B004957 ,wat.PRODUCT_ID --0668A ,p.PRODUCT_ID AS PRODUCT_NO --123 ,wat.TRIM_STAGE --WF_START ,wat.T_WAFER_ID --B004957-03 ,wat.LOT_ID --1708380 ,wat.WAFER_ID --6519598 6521374 ,wat.WAFER_NO --03 04 ,wat.TEST_PROGRAM --0668A_WATY ,PARA.WAT_PARAMETER_ID --123 ,PARA.PARAMETER_NAME --VTSAT_855PG_1_D020 ,PARA.limit_file ,MAX(wat.MEASURE_TIME) AS max_MEASURE_TIME --2025-03-07 FROM wat LEFT JOIN para on wat.TRIM_STAGE = para.STAGE_true AND wat.TEST_PROGRAM = para.TEST_PROGRAM AND wat.PRODUCT_ID = para.PRODUCT_ID LEFT JOIN PRODUCT p ON para.PRODUCT_ID = p.NAME --0668A GROUP BY wat.LOTNO --B004957 ,wat.PRODUCT_ID --0668A ,p.PRODUCT_ID --123 ,wat.TRIM_STAGE --WF_START ,wat.T_WAFER_ID --B004957-03 ,wat.LOT_ID --1708380 ,wat.WAFER_ID --6519598 6521374 ,wat.WAFER_NO --03 04 ,wat.TEST_PROGRAM --0668A_WATY ,PARA.WAT_PARAMETER_ID --123 ,PARA.PARAMETER_NAME --VTSAT_855PG_1_D020 ,PARA.limit_file HAVING PARA.PARAMETER_NAME=‘SRAM0015’ ) measuretime LEFT JOIN WAT_WAFER_INFO wwi – --------确认关联条件 参数 ON measuretime.max_MEASURE_TIME > wwi.MEASURE_TIME AND measuretime.TEST_PROGRAM = WWI.TEST_PROGRAM WHERE wwi.TEST_PROGRAM NOT LIKE ‘%M1%’ AND wwi.TEST_PROGRAM NOT LIKE ‘%M4%’ ORDER BY wwi.MEASURE_TIME desc
03-14
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值