帮我优化下这条sql:SELECT epi.business_id,epi.project_id,epi.product_setmeal_id,count(1) num FROM emp_info ei LEFT JOIN (select epi.* from emp_project_info epi where epi.status = 1 AND epi.is_across = 0 AND epi.start_date = ( SELECT max( start_date ) FROM emp_project_info WHERE STATUS = 1 AND emp_id = epi.emp_id AND project_id = epi.project_id AND start_date <= '2023-05-01 00:30:00.028' and is_across = 0 ) AND epi.begin_date <= '2023-05-01 00:30:00.028' AND (epi.end_date is null or epi.end_date > '2023-05-01 00:30:00.028')) epi ON ei.id = epi.emp_id WHERE ei.status = 1 AND ei.type = 3 AND epi.project_id is not null AND epi.source != 5 GROUP BY epi.business_id,epi.project_id,epi.product_setmeal_id
时间: 2024-03-29 22:36:56 浏览: 62
epi_edge.rar_EPI_edge detection
可以尝试以下优化:
1. 使用 EXISTS 替代子查询。
2. 将子查询中的 WHERE 条件移动到 JOIN 条件中。
3. 将比较运算符 "<=" 和 ">" 换成 BETWEEN。
4. 将 SELECT max(start_date) 改为 MAX(start_date) OVER(PARTITION BY emp_id, project_id)。
优化后的 SQL 如下:
SELECT epi.business_id, epi.project_id, epi.product_setmeal_id, COUNT(1) num
FROM emp_info ei
LEFT JOIN emp_project_info epi ON ei.id = epi.emp_id
WHERE ei.status = 1 AND ei.type = 3 AND epi.project_id IS NOT NULL AND epi.source != 5
AND epi.status = 1 AND epi.is_across = 0
AND epi.begin_date <= '2023-05-01 00:30:00.028'
AND (epi.end_date IS NULL OR epi.end_date > '2023-05-01 00:30:00.028')
AND epi.start_date BETWEEN MAX(epi.start_date) OVER(PARTITION BY epi.emp_id, epi.project_id) AND '2023-05-01 00:30:00.028'
GROUP BY epi.business_id, epi.project_id, epi.product_setmeal_id
这样可以避免子查询的计算,提高查询性能。
阅读全文