【sql-resolver】用sql-resolver给生产环境用到的SQL进行整形的效果陈列

原文：

select id,rank() over (order by id) as seq,sum(a) as sm,count(*) as cnt from emp

整理后：

select
    id,
    rank() over order by id  as seq,
    sum(a) as sm,
    count(*) as cnt
from
    emp

 

原文：

select code,cname,ename,type from t_dict_data where type='A' and flag=1 and code='B'

整理后：

select
    code,
    cname,
    ename,
    type
from
    t_dict_data
where
    type = 'A' and 
    flag = 1 and 
    code = 'B'

 

原文：

select b.vdate visitDate,nvl(c.capacity,b.vcnt) capacity from
(select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8) b
left join
(select to_char(a.create_time,'mm.dd') as visitDate,count(1) as capacity from
(select create_time,user_id from dceapp.t_user_log where create_time>=trunc(sysdate-6)) a
group by a.create_time
) c
o b.vdate=c.visitDate
order by b.vdate

整形后：

select
    b.vdate visitDate,
    nvl(c.capacity,b.vcnt) capacity
from
    (
    select
        to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,
        0asvcnt
    from
        dual
     connect by 
        level < 8) b left join 
    (
    select
        to_char(a.create_time,'mm.dd') as visitDate,
        count(1) as capacity
    from
        (
        select
            create_time,
            user_id
        from
            dceapp.t_user_log
        where
            create_time >= trunc(sysdate-6)) a
    group by
        a.create_time) c
order by
    b.vdate

 

原文：

select trade_date,sum(posi_qty) posi_qty from (
select dvttdi.trade_date,
coalesce(dtcotdd.posi_qty,0) posi_qty
from date_7 dvttdi
left join dws.dws_t_cli_opt_trade_data_d dtcotdd
on dvttdi.trade_date=dtcotdd.trade_date
where dvttdi.rn<=7
)

整形后：

select
    trade_date,
    sum(posi_qty) posi_qty
from
    (
    select
        dvttdi.trade_date,
        coalesce(dtcotdd.posi_qty,0) posi_qty
    from
        date_7 dvttdi left join 
        dws.dws_t_cli_opt_trade_data_d dtcotdd
    on
        dvttdi.trade_date = dtcotdd.trade_date
    where
        dvttdi.rn <= 7)

 

原文：

select a.id,a.hostname,a.disksize,a.used,to_char(a.ctime,'hh24:mi:ss') as nctime
from tmp a
left join tmp b
on a.ctime<b.ctime
where b.ctime is NULL

整形后：

select
    a.id,
    a.hostname,
    a.disksize,
    a.used,
    to_char(a.ctime,'hh24:mi:ss') as nctime
from
    tmp a left join 
    tmp b
on
    a.ctime < b.ctime
where
    b.ctime  is  NULL

 

END