2000中子查询运行缓慢的SQL计数

Question

我使用count语句进行SQL查询，并包含两个表之间的左联接。

但是，查询运行得非常慢，每次查询的结果出现在5-10分钟内。

希望任何人都能帮助我提高我正在做的以下查询的性能。

    select origin,count(*) as shipmentcount,
    (select count(*) as unprocessed  from tblshipmentdetail sd
    left join tblshipmentprocess sp on 
    sd.airbillno=sp.airbillno 
    where sp.airbillno is null and 
    sd.pickupdate >='2016-01-01' and sd.pickupdate <='2016-01-31'
    and sd.origin=a.origin
    ) as unprocessed
    from tblshipmentdetail a
    where pickupdate >='2016-01-01' and pickupdate <='2016-01-31'
    group by origin

Answer 1

您可以使用条件聚合而不是相关查询：

    select origin,count(*) as shipmentcount,
           count(case when sp.airbillno is null then 1 end) as unprocessed
    from tblshipmentdetail a
    LEFT OUTER JOIN tblshipmentprocess sp
     ON(a.airbillno=sp.airbillno)
    where a.pickupdate >='2016-01-01' and a.pickupdate <='2016-01-31'
    group by a.origin

Answer 2

请试试这个，这可能对你有帮助。

select origin,SUM(1) as shipmentcount,SUM(CASE WHEN sp.airbillno is NULL THEN 1 ELSE 0 END ) unprocessed
from tblshipmentdetail sd
left join tblshipmentprocess sp on  sd.airbillno=sp.airbillno 
where pickupdate >='2016-01-01' and pickupdate <='2016-01-31'
group by origin