如何在sql中找到客户明智类别中的最大产品id ?

Question

这里我有客户id和最大订单数量，我如何获得最大订单数量的productid列以及customerid和maxorderqty列。

数据库:adventure works
使用的表:salesoverheader,salesorderdetails

SELECT customerid,
Max(totalqty)
FROM   (SELECT customerid,
Sum(orderqty) AS Totalqty,
productid     AS pdtid
FROM   sales.salesorderheader a
INNER JOIN sales.salesorderdetail b
ON a.salesorderid = b.salesorderid
GROUP  BY customerid,
productid)A
WHERE  customerid = 29825
GROUP  BY customerid

Answer 1

如果您对查找单个记录感兴趣，您可以使用如下内容:

SELECT TOP(1) CustomerID, max(totalqty) AS maxqty, pdtid
FROM
(
SELECT customerid, Sum(orderqty) AS Totalqty, productid AS pdtid
FROM   sales.salesorderheader a
INNER JOIN sales.salesorderdetail b
ON a.salesorderid = b.salesorderid
GROUP  BY customerid, productid
) A
WHERE CustomerID=29825
GROUP BY CustomerID, pdtid
ORDER BY max(totalqty) DESC

但是…如果要查找具有相同级别的多条记录，请使用以下命令:

SELECT *
FROM 
(
SELECT RANK() OVER(ORDER BY max(totalqty) DESC) rnk, CustomerID, max(totalqty) AS maxqty, pdtid
FROM
(
SELECT customerid, Sum(orderqty) AS Totalqty, productid AS pdtid
FROM   sales.salesorderheader a
INNER JOIN sales.salesorderdetail b
ON a.salesorderid = b.salesorderid
GROUP  BY customerid, productid
) A
WHERE CustomerID=29825
GROUP BY CustomerID, pdtid
) B
WHERE rnk = 1

,db&lt的在小提琴

另一种方法是再次"加入"sailes的细节;)