mysql - 从子查询中获取计数

Question

我有 3 个表 ImportRecord、SiteOrder 和 Parcel，其中 ImportRecord.ID=SiteOrder.ImportId 和 SiteOrder.ID=Parcel.SiteOrderId。

我需要一个查询来检索以下内容:

declare @Started as varchar(50) = null
declare @Ended as varchar(50) = null
declare @ClientCode as varchar(50) = null
declare @FileName as varchar(50) = null
declare @PageSize as int = null

select  Count(so.ID) as TotalOrders, Count(p.ID) as TotalParcels,               
        --Count(so.Status <> 1 or so.Status <> 2) as TotalNotDespatched,
        --Count(so.Status = 3 or so.Status = 8 or so.Status = 7) as TotalInError,           
        ir.ID, ir.Filename, ir.Started, ir.Status, ir.ClientCode
from ImportRecord ir with (nolock)
join SiteOrder so with (nolock)
    on so.ImportId = ir.ID
join Parcel p with (nolock)
    on p.SiteOrderId = so.ID
where 1=1
  and ir.Status <> 5 --NOT DELETED
  and (@ClientCode is null or ir.ClientCode = @ClientCode)
  and (@Started is null or ir.Started = @Started)
  and (@Ended is null or ir.Ended = @Ended) 
  and (@ClientCode is null or ir.ClientCode = @ClientCode)
  and (@FileName is null or ir.Filename like '%' + @FileName + '%')
group by ir.ID, ir.Filename, ir.Started, ir.Status, ir.ClientCode
order by ir.ID desc

如何返回状态 <>1 或 <>2(TotalNotDespatched)以及 TotalInError 状态 = 3、7 和 8 的所有站点订单的计数？

Answer 1

使用条件聚合。 。 。 sum() 与 case:

select Count(so.ID) as TotalOrders, Count(p.ID) as TotalParcels,               
       sum(case when so.Status not in (1, 2) then 1 else 0 end) as TotalNotDespatched,
       sum(case when so.Status in (3, 7, 8) then 1 else 0 end) as TotalInError,           
       ir.ID, ir.Filename, ir.Started, ir.Status, ir.ClientCode
from ImportRecord ir with (nolock) join
     SiteOrder so with (nolock)
     on so.ImportId = ir.ID join
     Parcel p with (nolock)
     on p.SiteOrderId = so.ID
where 1=1
      ir.Status <> 5 --NOT DELETED and
      (@ClientCode is null or ir.ClientCode = @ClientCode) and
      (@Started is null or ir.Started = @Started) and
      (@Ended is null or ir.Ended = @Ended) and
      (@ClientCode is null or ir.ClientCode = @ClientCode) and
      (@FileName is null or ir.Filename like '%' + @FileName + '%') 
group by ir.ID, ir.Filename, ir.Started, ir.Status, ir.ClientCode
order by ir.ID desc;

我怀疑你的前两个值是你想要的。 TotalOrders 和 TotalParcels 通常具有相同的值。为什么？ COUNT() 计算非 NULL 值的数量，并且 id 通常不是 NULL。