多个内部联接和子查询的查询优化

debugcn 发表于 Dev

the_coder_guy

我需要有关以下查询的查询优化的帮助。

SELECT pr.todate , pr.descr, cmp.company_id 
FROM employee AS emp
INNER JOIN company AS cmp ON emp.emp_comp_id = cmp.company_id 
INNER JOIN profile AS pr ON emp.acca_id = pr.profile_id 
INNER JOIN acondition ON as_id = as_ac_id
WHERE as_closed = 0 
  AND (pr.ac_act_id = 20)
  AND (pr.todate = (SELECT MIN(todate) AS Expr1 
                    FROM profile pro 
                    INNER JOIN employee empl ON empl.acca_id = pro.profile_id 
                    JOIN acondition ON as_id = as_ac_id 
                    WHERE (pro.ac_act_id = 20 
                           AND empl.emp_comp_id = cmp.company_id) 
                      AND as_closed = 0))

由于主查询和子查询中存在重复的联接，是否有任何方法可以在子查询中删除这些联接？

戴尔K

正如您所阐明的那样，由于子查询几乎与主查询相同，因此您可以使用窗口函数RANK作为过滤条件。RANK为领带分配相同的编号，这意味着如果每个公司的多个记录匹配，您将全部获得它们，例如

SELECT todate, descr, company_id
FROM (
    SELECT pr.todate, pr.descr, cmp.company_id
      , RANK() OVER (PARTITION BY cmp.company_id ORDER BY pr.today ASC) RankNumber
    FROM employee AS emp
    INNER JOIN company AS cmp ON emp.emp_comp_id = cmp.company_id 
    INNER JOIN profile AS pr ON emp.acca_id = pr.profile_id 
    INNER JOIN acondition ON as_id = as_ac_id
    WHERE as_closed = 0 AND pr.ac_act_id = 20
) X
where RankNumber = 1;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。