Adventureworks练习Server

Question

使用SalesOrderHeader和SalesTerritory表，编写一个查询，根据Status字段计算每个地区的事务数量和每个区域的每月总金额。状态值​​为:1=进行中；2=已批准；3=回序；4=拒绝；5=已发运；6=取消结果表必须包含以下字段：

月:YYYY MM format

• NameTerritory : Territory

• TrProcess名称:处理中的事务:已批准的事务(Quantity)

• TrBackordered :过期的事务(Quantity)

• TrRejected :已拒绝的事务(Quantity)

• TrRejected:已发送的事务:已取消的事务amount

• MntShipped:总额sent

• MntCanceled:总额取消

​

​

SELECT 
    FORMAT(A.ShipDate, 'yyyy-MM') as Mes,
    B.Name,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 1) as TrProcess,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 2) as TrApproved,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 3) as TrBackordered,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 4) as TrRejected,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 5) as TrShipped,
    (SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 6) as TrCanceled,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 1) as MntProcess,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 2) as MntApproved,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 3) as MntBackordered,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 4) as MntRejected,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 5) as MntShipped,
    (SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 6) as MntCanceled
FROM [dbo].[SalesOrderHeader] A
INNER JOIN [dbo].[SalesTerritory] B on A.TerritoryID = B.TerritoryID
GROUP BY FORMAT(A.ShipDate,'yyyy/MM')

我已经设置了这个查询，但是我无法得到返回金额的查询。如果有人能帮我，我会很感激的。

Answer 1

您的逻辑有缺陷，主查询中的子查询和分支/日期之间没有关联，所以它们合计了所有内容。使用条件聚合说明了两者的区别。

SELECT 
    FORMAT(A.ShipDate, 'yyyy-MM') as mes,
    B.Name,
    (SELECT count(Status) FROM [sales].[SalesOrderHeader] where Status = 5) as TrProcess,
     sum(case when status = 5 then 1 else 0 end)  as trtprocesstrue

FROM [sales].[SalesOrderHeader] A
INNER JOIN [sales].[SalesTerritory] B on A.TerritoryID = B.TerritoryID
where b.name = 'northwest'
GROUP BY b.name,FORMAT(A.ShipDate, 'yyyy-MM') ;

mes        Name                                               TrProcess   trtprocesstrue
---------- -------------------------------------------------- ----------- --------------
2005-07    Northwest                                          31465       20
2005-08    Northwest                                          31465       28
2005-09    Northwest                                          31465       21
2005-10    Northwest                                          31465       22