how to reuse subquery result in mysql
- by chris
I'm doing a statistic job like this:
SUM |COND1 |COND2 |...
--------------------------
100 |80 | 70 |...
The SUM result is calculated from multiple tables, and the CONDs are subset of that.
I wrote a sql like this:
select tmp1.id,sum,cond1,cond2 as count from (
select id, count(*) as sum from table_1
group by table1.id) tmp1
left join (
select id, count(*) as cond1 from table1
where condition1
group by table1.id) tmp2 on tmp1.id=tmp2.id
left join (
select id, count(*) as cond2 from table1
where condition2
group by table1.id) tmp3 on tmp1.id=tmp3.id
the problem is that this is very poor efficiency, it will be better if i could use the result of tmp1, but i don't know how to do that.
update: simplified the sql, what i mean is how to reuse the first select result of table_1
