如此变态的sql，还能优化吗

Question

select count(KACount) as KaCount,a.ProvinceName,Max(totalCount) as totalCount,ItemTypeName,CompPrior,b.ItemTypePrior from (select f.CompPrior,count(KAID)totalCount,KAType,Province,ProvinceName,UPLoadFlag from (select KAID,KAType,UPLoadFlag from KAType)ka inner join (select ka_id,ka_name_ch,unicode from companykarelation) r on KAID=ka_id inner join (select uid,CompPrior,Province from f1stmap inner join (select invid,unicode uid from inventory where invclass1 is not null and invclass2='Y')inv on Fastracid=invid
 where Province in(@Province))f on Unicode=uid inner join (select F0003,lang01 ProvinceName from comment where F0001='Combo' and F0002='Province')c on Province=F0003 group by KAType,Province,ProvinceName,UPLoadFlag,f.CompPrior )a  inner join
(select Province,ProvinceName,CompPrior xwhid,xkaid,KAName,KAType,Count(xkaid) as KACount,ItemID,ItemTypeID,ItemTypeName,ItemTypePrior,ColorID,ColorName,UploadFlag from(select F0003,lang01 ProvinceName from comment where F0001='Combo' and F0002='Province' and  F0003 in(@Province) )lang inner join (select Fastracid,Province,CompName,compPrior from f1stmap)f on F0003=province inner join (select case ordertype when'2' then sendwhid else recvwhid end as xwhid,case ordertype when'4' then sendwhid else recvwhid end as xkaid,itemid xid,RecvQty from orderm m inner join orderd d on m.uniqueid=d.refkey where uploadtime >=@BeginDate and uploadtime<DateAdd(day,1,@EndDate) and (ordertype='2' or ordertype='4') and updateorder='0')o on Fastracid=xwhid inner join (select invid,unicode uid from inventory where invclass1 is not null and invclass2='Y')inv on Fastracid=invid inner join (select unicode,ka_id,ka_name_ch as KAName from companykarelation) c on xkaid=c.ka_id and uid=unicode inner join (select KAID,KAType,UploadFlag from KAType)ka on xkaid=KAID inner join (select itemid,ptacdes, colorid,itemtypeid from itemmap where brand='0')i on xid=itemid inner join (select itemtypeid xitid,itemtypeName,itemTypePrior from xItemType where brand='0' and ItemTypeId in(@ItemTypeID))xty on itemtypeid=xitid inner join (select colorid cid,ColorName from xItemColor where brand='0' and colorId in (@ColorID))xic on colorid=cid group by  Province,ProvinceName,CompPrior,xwhid,xkaid,KAName,KAType, ItemID,ItemTypeID,ItemTypeName,ItemTypePrior,ColorID,ColorName,UploadFlag having Sum(RecvQty)>=@STLower and Sum(RecvQty)<@STUpper) b on a.Province=b.Province
where a.UPLoadFlag=@UpLoadFlag and a.KAType in(@KAType) group by a.ProvinceName,b.ItemTypeName,a.CompPrior,b.ItemTypePrior order by Cast(a.CompPrior as float) desc,Cast(b.ItemTypePrior as float) desc

Answer 1

select
 count(KACount) as KaCount,a.ProvinceName,
 Max(totalCount) as totalCount,
 ItemTypeName,CompPrior,b.ItemTypePrior 
from
 (
select
 f.CompPrior,count(KAID)totalCount,KAType,Province,ProvinceName,UPLoadFlag 
from
 (select KAID,KAType,UPLoadFlag from KAType)ka 
inner join
 (select ka_id,ka_name_ch,unicode from companykarelation) r
on
 KAID=ka_id 
inner join
 (select uid,CompPrior,Province from f1stmap 
inner join
 (select invid,unicode uid from inventory where invclass1 is not null and invclass2='Y')inv 
on
 Fastracid=invid
where
 Province in(@Province))f on Unicode=uid i
nner join 
(
select
 F0003,lang01 ProvinceName 
from
 comment where F0001='Combo' and F0002='Province')c 
on
 Province=F0003 
group by
 KAType,Province,ProvinceName,UPLoadFlag,f.CompPrior )a  
inner join
(
select
 Province,ProvinceName,CompPrior xwhid,xkaid,KAName,KAType,
 Count(xkaid) as KACount,ItemID,ItemTypeID,ItemTypeName,ItemTypePrior,ColorID,ColorName,UploadFlag from
(select F0003,lang01 ProvinceName 
from
 comment where F0001='Combo' and F0002='Province' and  F0003 in(@Province) )lang inner join
 (select Fastracid,Province,CompName,compPrior from f1stmap)f 
on
 F0003=province 
inner join
 (
select
 case ordertype when'2' then sendwhid else recvwhid end as xwhid,
 case ordertype when'4' then sendwhid else recvwhid end as xkaid,
 itemid xid,RecvQty 
from
 orderm m inner join orderd d on m.uniqueid=d.refkey 
where
 uploadtime >=@BeginDate and uploadtime<DateAdd(day,1,@EndDate) and (ordertype='2' or ordertype='4') 
and
 updateorder='0')o on Fastracid=xwhid 
inner join
 (select invid,unicode uid from inventory where invclass1 is not null and invclass2='Y')inv 
on
 Fastracid=invid 
inner join
 (select unicode,ka_id,ka_name_ch as KAName from companykarelation) c 
on
 xkaid=c.ka_id and uid=unicode 
inner join
 (select KAID,KAType,UploadFlag from KAType)ka on xkaid=KAID 
inner join
 (select itemid,ptacdes, colorid,itemtypeid from itemmap where brand='0')i 
on
 xid=itemid 
inner join
 (select itemtypeid xitid,itemtypeName,itemTypePrior from xItemType where brand='0' and ItemTypeId in(@ItemTypeID))xty on itemtypeid=xitid 
inner join
 (select colorid cid,ColorName from xItemColor where brand='0' and colorId in (@ColorID))xic 
on
 colorid=cid 
group by
  Province,ProvinceName,CompPrior,xwhid,xkaid,KAName,KAType, ItemID,ItemTypeID,ItemTypeName,ItemTypePrior,ColorID,ColorName,UploadFlag 
having
 Sum(RecvQty)>=@STLower 
and
 Sum(RecvQty)<@STUpper) b on a.Province=b.Province
where
 a.UPLoadFlag=@UpLoadFlag and a.KAType in(@KAType) 
group by
 a.ProvinceName,b.ItemTypeName,a.CompPrior,b.ItemTypePrior 
order by
 Cast(a.CompPrior as float) desc,Cast(b.ItemTypePrior as float) desc

Answer 2

--实在是排版不下去了
select count(KACount) as KaCount
       ,a.ProvinceName
       ,Max(totalCount) as totalCount
       ,ItemTypeName
       ,CompPrior
       ,b.ItemTypePrior 
from (select f.CompPrior
             ,count(KAID)totalCount
             ,KAType
             ,Province
             ,ProvinceName
             ,UPLoadFlag 
      from (select KAID
                   ,KAType
                   ,UPLoadFlag 
            from KAType 
           ) ka 
           inner join (select ka_id
                              ,ka_name_ch
                              ,unicode 
                       from companykarelation
                      ) r on KAID=ka_id 
           inner join (select uid
                              ,CompPrior
                              ,Province 
                       from f1stmap 
                            inner join (select invid
                                               ,unicode uid 
                                        from inventory 
                                        where invclass1 is not null 
                                          and invclass2='Y'
                                       ) inv on Fastracid=invid
 where Province in(@Province))f on Unicode=uid inner join (select F0003,lang01 ProvinceName from comment where F0001='Combo' and F0002='Province')c on Province=F0003 group by KAType,Province,ProvinceName,UPLoadFlag,f.CompPrior )a  inner join
(select Province,ProvinceName,CompPrior xwhid,xkaid,KAName,KAType,Count(xkaid) as KACount,ItemID,ItemTypeID,ItemTypeName,ItemTypePrior,ColorID,ColorName,UploadFlag from(select F0003,lang01 ProvinceName from comment where F0001='Combo' and F0002='Province' and  F0003 in(@Province) )lang inner join (select Fastracid,Province,CompName,compPrior from f1stmap)f on F0003=province inner join (select case ordertype when'2' then sendwhid else recvwhid end as xwhid,case ordertype when'4' then sendwhid else recvwhid end as xkaid,itemid xid,RecvQty from orderm m inner join orderd d on m.uniqueid=d.refkey where uploadtime >=@BeginDate and uploadtime<DateAdd(day,1,@EndDate) and (ordertype='2' or ordertype='4') and updateorder='0')o on Fastracid=xwhid inner join (select invid,unicode uid from inventory where invclass1 is not null and invclass2='Y')inv on Fastracid=invid inner join (select unicode,ka_id,ka_name_ch as KAName from companykarelation) c on xkaid=c.ka_id and uid=unicode inner join (select KAID,KAType,UploadFlag from KAType)ka on xkaid=KAID inner join (select itemid,ptacdes, colorid,itemtypeid from itemmap where brand='0')i on xid=itemid inner join (select itemtypeid xitid,itemtypeName,itemTypePrior from xItemType where brand='0' and ItemTypeId in(@ItemTypeID))xty on itemtypeid=xitid inner join (select colorid cid,ColorName from xItemColor where brand='0' and colorId in (@ColorID))xic on colorid=cid group by  Province,ProvinceName,CompPrior,xwhid,xkaid,KAName,KAType, ItemID,ItemTypeID,ItemTypeName,ItemTypePrior,ColorID,ColorName,UploadFlag having Sum(RecvQty)>=@STLower and Sum(RecvQty)<@STUpper) b on a.Province=b.Province
where a.UPLoadFlag=@UpLoadFlag and a.KAType in(@KAType) group by a.ProvinceName,b.ItemTypeName,a.CompPrior,b.ItemTypePrior order by Cast(a.CompPrior as float) desc,Cast(b.ItemTypePrior as float) desc

Answer 3

select count(KACount) as KaCount ,
        a.ProvinceName ,
        Max(totalCount) as totalCount ,
        ItemTypeName ,
        CompPrior ,
        b.ItemTypePrior
    from ( select f.CompPrior ,
                count(KAID) totalCount ,
                KAType ,
                Province ,
                ProvinceName ,
                UPLoadFlag
            from ( select KAID ,
                        KAType ,
                        UPLoadFlag
                    from KAType
                 ) ka
                inner join ( select ka_id ,
                                    ka_name_ch ,
                                    unicode
                                from companykarelation
                           ) r on KAID = ka_id
                inner join ( select uid ,
                                    CompPrior ,
                                    Province
                                from f1stmap 
                                    inner join ( select invid ,
                                                        unicode uid
                                                    from inventory
                                                    where invclass1 is not null and
                                                        invclass2 = 'Y'
                                               ) inv on Fastracid = invid
                                where Province in ( @Province )
                           ) f on Unicode = uid 
                inner join ( select F0003 ,
                                    lang01 ProvinceName
                                from comment
                                where F0001 = 'Combo' and
                                    F0002 = 'Province'
                           ) c on Province = F0003
            group by KAType ,
                Province ,
                ProvinceName ,
                UPLoadFlag ,
                f.CompPrior
         ) a 
        inner join ( select Province ,
                            ProvinceName ,
                            CompPrior xwhid ,
                            xkaid ,
                            KAName ,
                            KAType ,
                            Count(xkaid) as KACount ,
                            ItemID ,
                            ItemTypeID ,
                            ItemTypeName ,
                            ItemTypePrior ,
                            ColorID ,
                            ColorName ,
                            UploadFlag
                        from ( select F0003 ,
                                    lang01 ProvinceName
                                from comment
                                where F0001 = 'Combo' and
                                    F0002 = 'Province' and
                                    F0003 in ( @Province )
                             ) lang
                            inner join ( select Fastracid ,
                                                Province ,
                                                CompName ,
                                                compPrior
                                            from f1stmap
                                       ) f on F0003 = province
                            inner join ( select case ordertype
                                                  when '2' then sendwhid
                                                  else recvwhid
                                                end as xwhid ,
                                                case ordertype
                                                  when '4' then sendwhid
                                                  else recvwhid
                                                end as xkaid ,
                                                itemid xid ,
                                                RecvQty
                                            from orderm m 
                                                inner join orderd d on m.uniqueid = d.refkey
                                            where uploadtime >= @BeginDate and
                                                uploadtime < DateAdd(day , 1 , @EndDate) and
                                                (
                                                  ordertype = '2' or
                                                  ordertype = '4'
                                                ) and
                                                updateorder = '0'
                                       ) o on Fastracid = xwhid
                            inner join ( select invid ,
                                                unicode uid
                                            from inventory
                                            where invclass1 is not null and
                                                invclass2 = 'Y'
                                       ) inv on Fastracid = invid
                            inner join ( select unicode ,
                                                ka_id ,
                                                ka_name_ch as KAName
                                            from companykarelation
                                       ) c on xkaid = c.ka_id and
                                              uid = unicode
                            inner join ( select KAID ,
                                                KAType ,
                                                UploadFlag
                                            from KAType
                                       ) ka on xkaid = KAID
                            inner join ( select itemid ,
                                                ptacdes ,
                                                colorid ,
                                                itemtypeid
                                            from itemmap
                                            where brand = '0'
                                       ) i on xid = itemid
                            inner join ( select itemtypeid xitid ,
                                                itemtypeName ,
                                                itemTypePrior
                                            from xItemType
                                            where brand = '0' and
                                                ItemTypeId in ( @ItemTypeID )
                                       ) xty on itemtypeid = xitid 
                            inner join ( select colorid cid ,
                                                ColorName
                                            from xItemColor
                                            where brand = '0' and
                                                colorId in ( @ColorID )
                                       ) xic on colorid = cid
                        group by Province ,
                            ProvinceName ,
                            CompPrior ,
                            xwhid ,
                            xkaid ,
                            KAName ,
                            KAType ,
                            ItemID ,
                            ItemTypeID ,
                            ItemTypeName ,
                            ItemTypePrior ,
                            ColorID ,
                            ColorName ,
                            UploadFlag
                        having Sum(RecvQty) >= @STLower and
                            Sum(RecvQty) < @STUpper
                   ) b on a.Province = b.Province
    where a.UPLoadFlag = @UpLoadFlag and
        a.KAType in ( @KAType )
    group by a.ProvinceName ,
        b.ItemTypeName ,
        a.CompPrior ,
        b.ItemTypePrior
    order by Cast(a.CompPrior as float) desc ,
        Cast(b.ItemTypePrior as float) desc

Answer 4

简单排版一遍select
     count(KACount) as KaCount
    ,a.ProvinceName
    ,Max(totalCount) as totalCount
    ,ItemTypeName
    ,CompPrior
    ,b.ItemTypePrior
from (
    select 
         f.CompPrior,count(KAID) totalCount,KAType,Province,ProvinceName,UPLoadFlag
    from (
        select KAID,KAType,UPLoadFlag 
        from KAType
    ) ka
    inner join (
        select ka_id,ka_name_ch,unicode 
        from companykarelation
    ) r on KAID=ka_id
    inner join (
        select uid,CompPrior,Province
        from f1stmap
        inner join (
            select invid,unicode uid 
            from inventory 
            where invclass1 is not null 
            and invclass2='Y'
        )inv on Fastracid=invid
        where Province in(@Province)
    )f on Unicode=uid
    inner join (
        select F0003,lang01 ProvinceName 
        from comment 
        where F0001='Combo' and F0002='Province'
    )c on Province=F0003
    group by KAType,Province,ProvinceName,UPLoadFlag,f.CompPrior
)a
inner join (
    select
         Province
        ,ProvinceName
        ,CompPrior xwhid
        ,xkaid
        ,KAName
        ,KAType
        ,Count(xkaid) as KACount
        ,ItemID
        ,ItemTypeID
        ,ItemTypeName
        ,ItemTypePrior
        ,ColorID
        ,ColorName
        ,UploadFlag
    from(
        select F0003,lang01 ProvinceName 
        from comment 
        where F0001='Combo' 
        and F0002='Province' 
        and F0003 in(@Province) 
    )lang
    inner join (
        select Fastracid,Province,CompName,compPrior 
        from f1stmap
    )f on F0003=province
    inner join (
        select
             case ordertype when'2' then sendwhid else recvwhid end as xwhid
            ,case ordertype when'4' then sendwhid else recvwhid end as xkaid
            ,itemid xid
            ,RecvQty
        from orderm m
        inner join orderd d on m.uniqueid=d.refkey
        where uploadtime >=@BeginDate 
        and uploadtime<DateAdd(day,1,@EndDate) 
        and (ordertype='2' or ordertype='4') 
        and updateorder='0'
    )o on Fastracid=xwhid
    inner join (
        select invid,unicode uid 
        from inventory 
        where invclass1 is not null 
        and invclass2='Y'
    )inv on Fastracid=invid
    inner join (
        select unicode,ka_id,ka_name_ch as KAName 
        from companykarelation
    ) c on xkaid=c.ka_id and uid=unicode
    inner join (
        select KAID,KAType,UploadFlag 
        from KAType
    )ka on xkaid=KAID
    inner join (
        select itemid,ptacdes, colorid,itemtypeid 
        from itemmap 
        where brand='0'
    )i on xid=itemid
    inner join (
        select itemtypeid xitid,itemtypeName,itemTypePrior 
        from xItemType 
        where brand='0' 
        and ItemTypeId in(@ItemTypeID)
    )xty on itemtypeid=xitid
    inner join (
        select colorid cid,ColorName 
        from xItemColor 
        where brand='0' 
        and colorId in (@ColorID)
    )xic on colorid=cid
    group by
         Province
        ,ProvinceName
        ,CompPrior
        ,xwhid
        ,xkaid
        ,KAName
        ,KAType
        , ItemID
        ,ItemTypeID
        ,ItemTypeName
        ,ItemTypePrior
        ,ColorID
        ,ColorName
        ,UploadFlag
    having Sum(RecvQty)>=@STLower 
    and Sum(RecvQty)<@STUpper
) b on a.Province=b.Province
where a.UPLoadFlag=@UpLoadFlag 
and a.KAType in(@KAType)
group by 
     a.ProvinceName
    ,b.ItemTypeName
    ,a.CompPrior
    ,b.ItemTypePrior
order by 
     Cast(a.CompPrior as float) desc
    ,Cast(b.ItemTypePrior as float) desc

Answer 5

服务器什么配置?这么复杂的sql也能跑.

Answer 6

    inner join (
        select Fastracid,Province,CompName,compPrior 
        from f1stmap
    )f on F0003=province==>
    inner join f1stmap f on F0003=f.province
    inner join (
        select itemid,ptacdes, colorid,itemtypeid 
        from itemmap 
        where brand='0'
    )i on xid=itemid[/code]==>
    inner join itemmap i on xid=i.itemid and i.brand='0'

Answer 7

会不会超出SQL的最大字符长度啊？

Answer 8

--去掉两个不带where 的子查询
select count(KACount) as KaCount ,
        a.ProvinceName ,
        Max(totalCount) as totalCount ,
        ItemTypeName ,
        CompPrior ,
        b.ItemTypePrior
    from ( select f.CompPrior ,
                count(KAID) totalCount ,
                KAType ,
                Province ,
                ProvinceName ,
                UPLoadFlag
            from  
                   KAType ka  --去掉此处子查询
                inner join  companykarelation r on ka.KAID = r.ka_id
                inner join ( select uid ,
                                    CompPrior ,
                                    Province
                                from f1stmap 
                                    inner join ( select invid ,
                                                        unicode uid
                                                    from inventory
                                                    where invclass1 is not null and
                                                        invclass2 = 'Y'
                                               ) inv on Fastracid = invid
                                where Province in ( @Province )
                           ) f on Unicode = uid 
                inner join ( select F0003 ,
                                    lang01 ProvinceName
                                from comment
                                where F0001 = 'Combo' and
                                    F0002 = 'Province'
                           ) c on Province = F0003
            group by KAType ,
                Province ,
                ProvinceName ,
                UPLoadFlag ,
                f.CompPrior
         ) a 
        inner join ( select Province ,
                            ProvinceName ,
                            CompPrior xwhid ,
                            xkaid ,
                            KAName ,
                            KAType ,
                            Count(xkaid) as KACount ,
                            ItemID ,
                            ItemTypeID ,
                            ItemTypeName ,
                            ItemTypePrior ,
                            ColorID ,
                            ColorName ,
                            UploadFlag
                        from ( select F0003 ,
                                    lang01 ProvinceName
                                from comment
                                where F0001 = 'Combo' and
                                    F0002 = 'Province' and
                                    F0003 in ( @Province )
                             ) lang
                            inner join  f1stmap  f on lang.F0003 = f.province  --去掉list子查询
                            inner join ( select case ordertype
                                                  when '2' then sendwhid
                                                  else recvwhid
                                                end as xwhid ,
                                                case ordertype
                                                  when '4' then sendwhid
                                                  else recvwhid
                                                end as xkaid ,
                                                itemid xid ,
                                                RecvQty
                                            from orderm m 
                                                inner join orderd d on m.uniqueid = d.refkey
                                            where uploadtime >= @BeginDate and
                                                uploadtime < DateAdd(day , 1 , @EndDate) and
                                                (
                                                  ordertype = '2' or
                                                  ordertype = '4'
                                                ) and
                                                updateorder = '0'
                                       ) o on Fastracid = xwhid
                            inner join ( select invid ,
                                                unicode uid
                                            from inventory
                                            where invclass1 is not null and
                                                invclass2 = 'Y'
                                       ) inv on Fastracid = invid
                            inner join ( select unicode ,
                                                ka_id ,
                                                ka_name_ch as KAName
                                            from companykarelation
                                       ) c on xkaid = c.ka_id and
                                              uid = unicode
                            inner join ( select KAID ,
                                                KAType ,
                                                UploadFlag
                                            from KAType
                                       ) ka on xkaid = KAID
                            inner join ( select itemid ,
                                                ptacdes ,
                                                colorid ,
                                                itemtypeid
                                            from itemmap
                                            where brand = '0'
                                       ) i on xid = itemid
                            inner join ( select itemtypeid xitid ,
                                                itemtypeName ,
                                                itemTypePrior
                                            from xItemType
                                            where brand = '0' and
                                                ItemTypeId in ( @ItemTypeID )
                                       ) xty on itemtypeid = xitid 
                            inner join ( select colorid cid ,
                                                ColorName
                                            from xItemColor
                                            where brand = '0' and
                                                colorId in ( @ColorID )
                                       ) xic on colorid = cid
                        group by Province ,
                            ProvinceName ,
                            CompPrior ,
                            xwhid ,
                            xkaid ,
                            KAName ,
                            KAType ,
                            ItemID ,
                            ItemTypeID ,
                            ItemTypeName ,
                            ItemTypePrior ,
                            ColorID ,
                            ColorName ,
                            UploadFlag
                        having Sum(RecvQty) >= @STLower and
                            Sum(RecvQty) < @STUpper
                   ) b on a.Province = b.Province
    where a.UPLoadFlag = @UpLoadFlag and
        a.KAType in ( @KAType )
    group by a.ProvinceName ,
        b.ItemTypeName ,
        a.CompPrior ,
        b.ItemTypePrior
    order by Cast(a.CompPrior as float) desc ,
        Cast(b.ItemTypePrior as float) desc