如何将选择子查询重构为连接? [英] How to refactor select subqueries into joins?

问题描述

我有一个极其丑陋的复杂查询，它根据一系列分组返回比赛结果.

I have an extremely ugly complex query that returns results for a race based on a series of groupings.

我有一个包含注册信息(姓名等)的表和一个包含完成时间的表.我需要为每个种族、班级和部门(它们是彼此的子组)找到一个位置.

I have a table that contains registration information (name, etc), and a table that contains finish times. I need to get a place for each race, class, and division (they are sub groups of each other).

我现在的工作是这样的:

What I have working right now is this:

Select reg.name, r.fin_time
  ( select count(*)+1 from 
    ( Select temp_reg.id as regid, temp_reg.race_id as in_race, temp_res.division as in_div, 
         COALESCE(temp_res.fin_time, 9999999999) as finish_time 
      FROM results as temp_res Inner JOIN registration as temp_reg on temp_res.registration_id = temp_reg.id 
      WHERE COALESCE(temp_res.fin_time) > 0 
        AND temp_res.status ='Finished') as ahead_div 
    WHERE ahead_div.regid <> reg.registration_id 
      AND ahead_div.in_race = reg.race_id 
      AND ahead_div.in_div = r.division 
      AND ahead_div.finish_time < COALESCE(r.fin_time, 9999999999) ), '-') as fin_place_div,
... same for class and race...
FROM results as r inner join registration as reg on r.registration_id = reg.id 
WHERE r.status = 'Finished';

所以我收集了所有完成的参赛者，并计算他们在同一比赛和同一 div 中的位置，并且他们的完成时间是

此记录的完成时间.

So I am collecting all the competitors that are finished, and counting them where they are in the same race and same div, and their finish time is < the finish time of this record.

我现在有一种情况，我需要将计算的比赛终点位置显示为班级终点位置..所以我需要在 if 语句中复制比赛位置子查询(丑陋且缓慢)，或者移动子查询到 where 子句，以便我可以多次使用它们.

I now have a situation where I need the calculated race finish place to be displayed as the class finish place.. so I need to either duplicate the race position subquery in an if statement (uglier and slow), or move the subqueries to the where clause so I can use them more than once.

如何将此查询从count() from subquery where"单值子查询中的选择重新格式化为连接?

How would I reformat this query from a "count() from subquery where" single value subquery in the select to a join?

有什么我可以重用内部子查询的吗?如果将班级添加到其中，我可以从中计算出每个计数的位置.

Is there some what I can reuse the inner subquery? if in add the class to it, I could count from it for every place counting.

干杯，

推荐答案

我知道这不会让你的代码看起来更干净，但是如果你只是重复使用相同的子查询，它很可能会被缓存，因此性能方面应该'不是性能损失(通常只要它不使用会话变量或者是相关子查询).

I know this won't help your code look any cleaner, but performance wise if you simply re-use the same subquery it will likely be cached so there shouldn't be a performance hit (generally as long as it doesn't use session variables or is a correlated subquery).

看看 EXPLAIN EXTENDED 说了什么，如果它没有返回 DEPENDENT SUBQUERY 或 UNREACHABLE SUBQUERY，就不应该有性能命中.

See what EXPLAIN EXTENDED says and provided it doesn't come back with DEPENDENT SUBQUERY or UNREACHABLE SUBQUERY there shouldn't be a performance hit.

SELECT
   *
FROM
   (SELECT a FROM a) b INNER JOIN
   (SELECT a FROM a) c ON b.a = c.a

查询优化器应该缓存子查询，这样它就不会被实际检索两次.

The query optimizer should cache the subquery so that it is not actually retrieved twice.

这篇关于如何将选择子查询重构为连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！