mysql 复用 子查询_在InnerJoin中重用mysql子查询

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.

The original query

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

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

解决方案

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.

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.

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.