select distinct right(AUFNR,7) as orderNumber
               ,auart 
               ,case when exists(select top 1 1 from covp where kstar = '0060305114' and OBJNR = a.OBJNR) 
               then '非正常' else '正常' end as orderType
               ,case (select top 1 left(MATNR,1) from covp where KSTAR = '0060392221' and OBJNR = a.OBJNR and MATNR <>'')
               when '3' then 'A'
               when '5' then 'B'
               else null 
               end AS BU
from aufk a
where a.AUFNR not like 'Q%'

select kstar ,OBJNR ,MATNR  
into #temp
from covp where kstar in( '0060305114' ,'0060392221')

select distinct right(AUFNR,7) as orderNumber
               ,auart 
               ,case when exists(select top 1 1 from #temp where kstar = '0060305114' and OBJNR = a.OBJNR) 
               then '非正常' else '正常' end as orderType
               ,case (select top 1 left(MATNR,1) from #temp where KSTAR = '0060392221' and OBJNR = a.OBJNR and MATNR <>'')
               when '3' then 'A'
               when '5' then 'B'
               else null 
               end AS BU
from aufk a
where a.AUFNR not like 'Q%'

------解决方案--------------------
对 aufk 表的 aufnr,objnr; covp 表的 kstar 创建索引后再试.

------解决方案--------------------
探讨

引用:

对 aufk 表的 aufnr,objnr; covp 表的 kstar 创建索引后再试.

条件不允许，会影响到ETL速度


------解决方案--------------------
探讨

引用:
引用:

对 aufk 表的 aufnr,objnr; covp 表的 kstar 创建索引后再试.

条件不允许，会影响到ETL速度


那您就慢慢跑吧.


------解决方案--------------------
探讨


引用:
引用:
引用:

对 aufk 表的 aufnr,objnr; covp 表的 kstar 创建索引后再试.

条件不允许，会影响到ETL速度


那您就慢慢跑吧.

呵呵，#temp用不了的话也慢慢跑吧


------解决方案--------------------
where a.AUFNR not like 'Q%'
改成
where left(a.AUFNR, 1)<>'Q'
这个会不会好点？

------解决方案--------------------

SQL code

Select distinct right(A.AUFNR,7) as orderNumber,A.auart ,
        Case When isNull(C1.Objnr,'')='' Then '非正常' else '正常' end as orderType,
        Case C2.m1 when '3' then 'A' when '5' then 'B' else null  end AS BU 
From aufk A Left Join covp C1 On (A.Objnr = C1.Objnr And C1.kstar = '0060305114')
    Left Join (select top 1 left(MATNR,1) as m1, Objnr from covp) C2 
    On (A.Objnr = C2.Objnr And C2.kstar = '0060392221' And MATNR <> '')
where Left(a.AUFNR,1) <>  'Q'

------解决方案--------------------
SQL code
with temp
as(
select kstar ,OBJNR ,MATNR  
from covp where kstar in( '0060305114' ,'0060392221'))

select distinct right(AUFNR,7) as orderNumber
               ,auart 
               ,case when exists(select top 1  from temp where kstar = '0060305114' and OBJNR = a.OBJNR) 
               then '非正常' else '正常' end as orderType
               ,case (select top 1 left(MATNR,1) from temp where KSTAR = '0060392221' and OBJNR = a.OBJNR and MATNR <>'')
               when '3' then 'A'
               when '5' then 'B'
               else null 
               end AS BU
from aufk a
where a.AUFNR not like 'Q%'

------解决方案--------------------