在InnerJoin中重用mysql子查询 [英] Reuse mysql Subquery in InnerJoin

问题描述

我正在尝试优化查询，试图避免重复使用" COMPLEX QUERY "指示的查询，该查询使用了2次且两次都具有相同的结果.

I'm trying optimizing a query, trying to avoid repeating the query indicated with "COMPLEX QUERY", that is used 2 times and both, has the same results.

原始查询

SELECT news.* 
FROM   news 
   INNER JOIN((SELECT myposter 
               FROM   (SELECT **COMPLEX QUERY**)) 
              UNION 
              (SELECT myposter 
               FROM   `profiles_old` prof2 
               WHERE  prof2.profile_id NOT IN (SELECT **COMPLEX QUERY**))) r 
           ON news.profile = r.p 

我在想这样的事情是否可能:

I was wondering if something like this was possible:

SELECT news.* 
FROM   (SELECT **COMPLEX QUERY**) complexQuery, 
   news 
   INNER JOIN ((SELECT myposter 
                FROM   complexquery) 
               UNION 
               (SELECT myposter 
                FROM   `profiles_old` prof2 
                WHERE  prof2. profile NOT IN (SELECT myposter 
                                              FROM complexQuery))) r 
           ON news. profile = r.p 

Mysql会进行某种类型的此类查询缓存吗?

Does Mysql do some sort of caching of that type of query?

推荐答案

您的问题的直接答案是否". MySQL不支持您想要的.您真正想要的是with语句.很棒的声明！如果需要，请使用其他数据库. las.

The direct answer to your question is "no". MySQL does not support what you want. What you really want is the with statement. Great statement! If you want it, use a different database. Alas.

我认为您可以执行此操作，尽管方法与您正在执行的操作完全不同.

I do think you can do this, although the approach is quite different from what you are doing.

逻辑是从复杂查询中获取所有myposter的值，并从profiles_old中获取所有myposter的值，其中相应的profile_id不在复杂查询中.

The logic is to take all values of myposter from the complex query and to take all values of myposter from profiles_old where the corresponding profile_id is not in the complex query.

您可以使用union all和聚合来执行此操作.只关注内部子查询:

You can do this with union all and aggregation. Just focusing on the inner subquery:

select (case when max(which = 'cq') = 1 then myposter
             when max(which = 'po') = 1 and max(which = 'cq') = 0 then id2
       ) as myposter
from (select myposter, myposter as id2, 'cq' as which
      from (select **complex query**) cq
      union all
      select profile_id, myposter, 'po' as which
      from profiles_old
     ) t
group by myposter;

其余只是将其合并到您的整体查询中.

The rest is just incorporating this into your overall query.

这篇关于在InnerJoin中重用mysql子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！