GBASE分享：SQL优化

笛卡尔积语法优化

ORACLE原始语法：

select xm.sfzh,xm.xm,ph.phone,ad.address

from ods.ods_connect_list_xm xm,ods.ods_connect_list_address ad,ods.ods_connect_list_phone ph

where xm.sfzh=ad.sfzh(+)

and xm.sfzh=ph.sfzh(+)

and ph.phone=’2259261′

group by xm.sfzh,xm.xm,ph.phone,ad.address;

GBase优化语法：

select xm.sfzh,xm.xm,ph.phone,ad.address

from (select distinct sfzh,xm from ods.ods_connect_list_xm) xm

left join (select distinct sfzh,address from ods_connect_list_address) ad on xm.sfzh=ad.sfzh

left join (select distinct sfzh,phone from ods.ods_connect_list_phone where phone=’2259261′) ph on xm.sfzh=ph.sfzh

group by xm.sfzh,xm.xm,ph.phone,ad.address;

oracle语法：

select

count(case when (NOT EXISTS(SELECT 1 FROM TJK.WAJ04 B WHERE B.WJ005=”AAC009” AND IY02.AAC009 = B.WJ014) and IY02.AAC009 is not null) then ”1” else null end) as “83”

from iy02 where …………..

GBase语法：

select

count(case when (t1.WJ014 is null and IY02.AAC009 is not null) then ”1” else null end) as “83”

from iy02

left join

(select distinct WJ014 from WAJ04  )t1  on IY02.AAC009 = t1.WJ014

where …………..