SQL语句优化

[chinabdx]

declare @deptcode varchar(4),
        @startdate datetime,
        @enddate datetime
set @deptcode = '0201'
set @startdate = '2016-07-01 00:00'
set @enddate = '2016-07-22 23:59'

SELECT c.stddeptcode,
         c.stddeptname,         
         sum(a.actualmoney) as '销售额',
         count(distinct a.flowno) as '客流量',
         convert(decimal(18,2),round(sum(a.actualmoney)/count(distinct a.flowno),2)) as '客单价'        
    FROM inf_gdscode b
    left join acc_goodsflow a on b.gdscode = left(a.gdssalecode,8) 
        and a.deptcode = @deptcode
        and a.saletime >= @startdate  
        AND a.saletime <= @enddate
     left join inf_stddept c on b.stddept = c.stddeptcode
     where c.stddeptcode in (21,22,23)
 group by c.stddeptcode,
          c.stddeptname

这个语句还有没有优化的空间了
c.stddeptcode in (21,22,23)这句能不能用Exists进行过滤

[唐诗三百首]

建索引 create index ix_acc_goodsflow_saletime_deptcode on acc_goodsflow(saletime,deptcode) include(gdssalecode), 然后用如下写法试试,

declare @deptcode varchar(4),
        @startdate datetime,
        @enddate datetime

set @deptcode = '0201'
set @startdate = '2016-07-01 00:00'
set @enddate = '2016-07-22 23:59'

select c.stddeptcode,
       c.stddeptname,         
       sum(a.actualmoney) as '销售额',
       count(distinct a.flowno) as '客流量',
       convert(decimal(18,2),round(sum(a.actualmoney)/count(distinct a.flowno),2)) as '客单价'        
 from inf_gdscode b
 left join (select gdssalecode=left(gdssalecode,8),
                   actualmoney,
                   flowno
            from acc_goodsflow 
            where deptcode=@deptcode
            and saletime>=@startdate  
            and saletime<=@enddate) a on b.gdscode=a.gdssalecode
 left join inf_stddept c on b.stddept = c.stddeptcode
 where c.stddeptcode in(21,22,23)
 group by c.stddeptcode,c.stddeptname

[kingtiy]

 select d.*,c.stddeptname from (
SELECT b.stddept ,      
         sum(a.actualmoney) as '销售额',
         count(distinct a.flowno) as '客流量',
         convert(decimal(18,2),round(sum(a.actualmoney)/count(distinct a.flowno),2)) as '客单价'        
    FROM inf_gdscode b
    left join acc_goodsflow a on b.gdscode = left(a.gdssalecode,8) 
        and a.deptcode = @deptcode
        and a.saletime >= @startdate  
        AND a.saletime <= @enddate     
     where b.stddept  between  21 and 23
 group by b.stddept 
		)  d  left join inf_stddept c on b.stddept = d.stddeptcode
--这样试下?

[chinabdx]

都不是特别理想

[chinabdx]

跟原来区别不大

[卖水果的net]

1.     left join acc_goodsflow a on b.gdscode = left(a.gdssalecode,8)  表 a 就用不到索引了 2.      left join inf_stddept c on b.stddept = c.stddeptcode      where c.stddeptcode in (21,22,23) 换成 inner join ，并且把 where 换成  b.stddept  in  (21,22,23)

[chinabdx]

谢谢三位大神了