无法获取目标销售总额

Question

原始问题

使用AdventureWorks2008R2编写一个查询，返回每个销售人员的销售人员id、销售的唯一产品数量、最高订单价值、总销售金额和前3个订单。

在计算最高订单值和总销售金额时，使用SalesOrderHeader中的TotalDue。前3个订单的总订单量最高。如果出现平局，则必须取回平局。排除没有销售人员用于此查询的订单。只返回总销售额为超过9800000美元。

返回订单值和总销售额为int。返回的数据按SalesPersonID排序。返回的数据应具有如下所示的格式。使用示例格式仅用于格式化目的。

结果

订单价值275242165029>276244145742td style="text-align:center；">277246>td style="text align:right；">132728

Answer 1

您将不得不更改其中的对象名称。此代码假定存在两个表：

CREATE TABLE InvoiceItems (InvoiceItemID BIGINT IDENTITY(1,1) NOT NULL, InvoiceID BIGINT NOT NULL, Sequence INT NULL, Quantity INT NULL, Cost DECIMAL(7, 2) NULL, Price DECIMAL(7, 2) NULL, ProductID BIGINT NULL)
CREATE TABLE Invoices (InvoiceID BIGINT IDENTITY(1,1) NOT NULL, InvoiceDateTimeUTC DATETIME2(7) NULL, CustomerID BIGINT NULL, EmployeeID BIGINT NULL, StoreID INT NULL)

(这些是我维护的脚本的一部分，该脚本生成完全随机的销售数据，具有适当的约束和各种乐趣)

代码应该提供一个生成您想要的结果的方法示例。

USE RandomSales;
;WITH Sales AS (
SELECT i.InvoiceID, i.InvoiceDateTimeUTC, i.CustomerID, i.EmployeeID, i.StoreID, ii.InvoiceItemID, ii.Sequence, ii.Quantity, ii.Cost, ii.Price, ii.ProductID, 
ii.Price*ii.Quantity AS ItemTotal, 
ii.Cost*ii.Quantity AS ItemValue, 
SUM(ii.Price*ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS InvoiceTotal,
SUM(ii.Cost*ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS InvoiceValue,
SUM(ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS QuantityTotal
FROM dbo.Invoices i
INNER JOIN dbo.InvoiceItems ii
ON ii.InvoiceID = i.InvoiceID
), QtyRankByEmployee AS (
SELECT EmployeeID, STRING_AGG(InvoiceId,', ') AS Invoices
FROM (
SELECT Sales.EmployeeID, Sales.InvoiceID, MAX(Sales.QuantityTotal) AS Qty, RANK() OVER (PARTITION BY Sales.EmployeeID ORDER BY MAX(Sales.QuantityTotal) DESC) AS QtyRank
FROM Sales
GROUP BY Sales.EmployeeID, Sales.InvoiceID
) a
WHERE a.QtyRank <= 3
GROUP BY a.EmployeeID
), EmployeeTotal AS (
SELECT EmployeeID, COUNT(DISTINCT ProductID) AS TotalUniqueProducts, SUM(InvoiceValue) AS TotalValue, SUM(InvoiceTotal) AS TotalSales
FROM Sales s
GROUP BY EmployeeID
)
SELECT e.EmployeeID, e.TotalUniqueProducts, e.TotalValue, e.TotalSales, q.Invoices
FROM EmployeeTotal e
LEFT OUTER JOIN QtyRankByEmployee q
ON e.EmployeeID = q.EmployeeID

EmployeeID
1		2		3			4		5		6		7		8		9		10		11		12		13		14		15		16		17