mySQL没有 with as

注：此文就是发发牢骚！

之前一直用oracle ,SQLServer等，用with as 作为虚拟表去处理数据很上手，忽然，用mySQL去处理数据时，才发现mySQL不支持with as ，让人火大，本来很短的SQL语句，愣是放大拉长了，例子如下：

1.使用 with as:

with c as(

select sum(c.amt)/100 pay_amt

from c

)

select a.*,b.*,d.*

from c a

left join c b on a.settle_date=b.settle_date

left join c d on d.settle_date=a.settle_date

2.未使用with as:

select a.*,b.*,d.*

from(

select sum(c.amt)/100 pay_amt

from c) a

left join 

(

select sum(c.amt)/100 pay_amt

from c) 

b on a.settle_date=b.settle_date

left join 

(

select sum(c.amt)/100 pay_amt

from c) 

d on d.settle_date=a.settle_date