二维码

小白菜求助,SQL限制条件优化

alex发表于 2017-12-29 16:25Twilight 最后回复于 2018-01-02 09:56 [复制链接] 78 2

最近遇到了一个个人觉得比较变态的限制条件,严重影响执行速度,个人技能有限,还没法对他进行优化,跪求高人指点,代码如下
  1. SELECT
  2.   '补充' AS dataclass
  3. , blart
  4. , budat
  5. , budat_1
  6. ,xblnr
  7. , mblnr
  8. , mjahr
  9. , zeile
  10. , bwart
  11. , sku
  12. ,CASE WHEN bwart IN ('641','642') AND sobkz = 'K' THEN ltrim (umwrk,'0')
  13. ELSE  ltrim (werks,'0') END AS werks
  14. ,CASE when (bwart in ('102','101','305','306','Z07','Z08'))
  15. THEN '' ELSE lgort END  AS lgort
  16. --, a.werks
  17.   --, a.lgort
  18.   , charg
  19.   , sobkz
  20.   , lifnr
  21.   , shkzg
  22.   , kokrs
  23.   , gjahr
  24.   , bukrs
  25.   , waers
  26.   , dmbtr*-1 as dmbtr
  27.   , menge*-1 as menge
  28.   , vlfkz
  29.   , mtart
  30.   , wempf
  31.   , umwrk
  32.   , xauto
  33.   , kzzug
  34.   , wgbez_1
  35.   , zyear
  36.   , kbetr
  37.   , band
  38.   , zdate
  39.   , ukurs
  40.   , shop_number
  41.   from dwd.dwd_ret_exf_mdkcls
  42.   where (vlfkz = 'B' and sobkz ='K'and bwart ='101'and shkzg= 'S'and kzzug='X'and ltrim (werks,'0') in ('DC20','DC21','DC22'))
  43.   or (vlfkz = 'B' and sobkz ='K' and bwart ='207'and shkzg= 'S')
  44.   or (vlfkz = 'B' and sobkz ='K' and bwart ='305'and shkzg= 'S'and ltrim (werks,'0') in ('DC20','DC21','DC22'))
  45.   or (vlfkz = 'B' and sobkz ='K' and bwart ='208'and shkzg= 'H')
  46.   or (vlfkz = 'B' and sobkz ='K'and lgort in ('1400','1100','1000')and bwart ='315'and shkzg= 'S')
  47.   or (vlfkz = 'A' and sobkz ='K'and bwart ='305'and shkzg= 'S')
  48.   or (vlfkz = 'A' and sobkz ='K'and bwart ='306'and shkzg= 'H')
  49.   or (vlfkz = 'A' and sobkz ='K'and bwart ='101'and shkzg= 'S'and  ltrim (werks,'0') in ('AU07','DU01','DU02'))
  50.   or (vlfkz = 'B' and sobkz ='K' and lgort in ('100','200') and bwart ='102'and shkzg= 'H'and kzzug='X'and ltrim (werks,'0') in ('DC20','DC21','DC22'))
复制代码

回复

使用道具 举报

zhongguomao
丝毫看不懂啊。
回复 支持 反对

使用道具 举报

Twilight
这通常是非SAP开发的ITer写出的代码,直接在数据库上建视图写完代码,在贴到SAP中,称为native sql,如果数据库时oracle,则为oracle sql语法,网上直接查看相应语法即可,其中ltrim(werks,'0'),是sql常用函数,表示去除地点中的0,这种写法是纯技术,不理解业务含义,维护时心累,强烈不推荐这种写法
回复 支持 反对

使用道具 举报

快速回帖

本版积分规则
您需要登录后才可以回帖 登录 | 注册有礼

快速回复 返回顶部 返回列表