注:此文就是发发牢骚!
之前一直用oracle ,SQLServer等,用with as 作为虚拟表去处理数据很上手,忽然,用mySQL去处理数据时,才发现mySQL不支持with as ,让人火大,本来很短的SQL语句,愣是放大拉长了,例子如下:
1.使用 with as:
with c as(
select sum(c.amt)/100 pay_amt
from c
)
selecta.*,b.*,d.*
from c a
left join c b ona.settle_date=b.settle_date
left join c d ond.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