时间戳

首页 > 解决方案 > 如何在sql中找到客户明智类别中的最大产品ID？

sql - 如何在sql中找到客户明智类别中的最大产品ID？

问题描述

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

数据库：adventure works
使用的表：salesorerheader,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

标签： sqlsql-servergroup-bymax

解决方案

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

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<>小提琴

另一种方法是再次“加入”航行细节；）

推荐阅读