Question

帮忙优化一个mysql的语句，很多重复子查询

select distinct (select sum(value1) as sum1 from table1 where recdate=(select max(recdate) as max from table1)) as sum1,(select sum(value2) as sum2 from table1 where recdate=(select max(recdate) as max from table1)) as sum2,recdatefrom table1 where recdate = (select max(recdate) as max from table1)

说明 (select max(recdate) as max from table1)这个子查询用到3次，而且所有查询都是在一张表里

Answer 1

子查询优化策略

对于不同类型的子查询，优化器会选择不同的策略。

1. 对于 IN、=ANY 子查询，优化器有如下策略选择：

• semijoin

• Materialization

• exists

2. 对于 NOT IN、<>ALL 子查询，优化器有如下策略选择：

• Materialization

• exists

3. 对于 derived 派生表，优化器有如下策略选择：

• derived_merge，将派生表合并到外部查询中（5.7 引入 ）；

• 将派生表物化为内部临时表，再用于外部查询。

注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略

Answer 2

看代码看的晕，给你个建议吧，就不仔细看你的东西了
一个是做临时表，分别几个不同的条件放到几个临时表，然后最后一个语句进行总结处理

另外一个就是，拆分，比如说一个表table
你可以
select * from table a,table b
where a.recdate=b.recdate

Answer 3

select sum(value1) as sum1,sum(value2) as sum2,recdate
from table1
where recdate = (select max(recdate) as max from table1)
group by recdate

追问

select distinct (select sum(value) as sum1 from table1 where type='system' and recdate=(select max(recdate) as max from table1)) as sum1,(select sum(value) as sum2 from table1 where type='user' and recdate=(select max(recdate) as max from table1)) as sum2,recdatefrom table1 where recdate = (select max(recdate) as max from table1)

那这个可以优化吗？value1和value2通过type来区分，能优化再加分

追答

可以的！~
select sum(case when type='system' then value1 else 0 end) as sum1,sum(case when type='user' then value2 else 0 end) as sum2,recdate
from table1
where recdate = (select max(recdate) as max from table1)
group by recdate