时间戳

首页 > 解决方案 > 使用嵌套聚合查询的最大 SUM

sql-server - 使用嵌套聚合查询的最大 SUM

问题描述

2 个月前，我开始在我正在关注的 DataScience MSccursur 的上下文中学习 SQL。

使用 SQL SERVER 和示例数据库 WideWorldImporters，我必须构建一个查询来为每个客户类别选择与未在发票中转换的订单相关的最大损失的客户。

我必须得到的结果如下表：

在此处输入图像描述

我构建了以下查询：

SELECT  CustomerCategoryName,
    MAX(SumLossesOrdersNotConverted) As MaxLoss
FROM
(   SELECT  Cat.CustomerCategoryName,
    SUM(OL.Quantity * OL.UnitPrice) as SumLossesOrdersNotConverted,
    C.CustomerName AS CustName,
    C.CustomerID AS CustID
    FROM    Sales.OrderLines AS OL 
        JOIN Sales.Orders O ON OL.OrderID = O.OrderID
        JOIN Sales.Customers AS C ON C.CustomerID = O.CustomerID
        JOIN Sales.CustomerCategories AS Cat ON Cat.CustomerCategoryID = C.CustomerCategoryID
    WHERE
        NOT EXISTS
        (
            SELECT *
            FROM Sales.Invoices as I
            WHERE O.OrderID = I.OrderID
        )
    GROUP BY C.CustomerID,C.CustomerName,Cat.CustomerCategoryName
) AS LossSummary
GROUP BY CustomerCategoryName
ORDER BY MAX(SumLossesOrdersNotConverted) DESC

并且能够获得前 2 列：

在此处输入图像描述

但是，尽管进行了很多搜索和努力，我仍然无法添加相应的 CustomerName 和 CustomerID 列。

当然，我尝试在外部选择中添加 CustName、CustId。但它并没有给我预期的结果，因为我必须在 GROUP BY 中添加 CustName、CustId：

在此处输入图像描述

我需要一个表，每个类别都有 maxloss 和相应的 customerid

任何帮助将不胜感激。提前谢谢。

标签： sql-serversubqueryaggregate-functions

解决方案

这行吗？

WITH
    LossSummary AS (
        SELECT  Cat.CustomerCategoryName,
                SUM(OL.Quantity * OL.UnitPrice) as SumLossesOrdersNotConverted,
                C.CustomerName AS CustName,
                C.CustomerID AS CustID,
                ROW_NUMBER() OVER (PARTITION BY Cat.CustomerCategoryName ORDER BY SUM(OL.Quantity * OL.UnitPrice) DESC) AS [Order]
        FROM    Sales.OrderLines AS OL 
        JOIN    Sales.Orders O ON OL.OrderID = O.OrderID
        JOIN    Sales.Customers AS C ON C.CustomerID = O.CustomerID
        JOIN    Sales.CustomerCategories AS Cat ON Cat.CustomerCategoryID = C.CustomerCategoryID
        WHERE   NOT EXISTS (
                    SELECT *
                    FROM Sales.Invoices as I
                    WHERE O.OrderID = I.OrderID
                )
        GROUP BY C.CustomerID,C.CustomerName,Cat.CustomerCategoryName
    )
SELECT  *
FROM    LossSummary
WHERE   [Order] = 1
;

推荐阅读