嘿，我明白你想要让订单总量最多的客户排在结果最前面的需求了！咱们来一步步调整你的存储过程：

问题分析 #

你的原存储过程是按Item.Item_Name分组，这样会把同一个客户的不同商品分开统计。要实现按客户订单总量排序，我们需要：

• 修正分组逻辑，确保符合SQL语法要求（非聚合字段必须出现在GROUP BY中）；
• 添加排序规则，优先按客户的总订单数降序排列，再按单商品的订单数降序。

修改后的存储过程代码 #

BEGIN
    SELECT 
        Customer.Customer_Name,
        Item.Item_Name,
        COUNT(Item.Item_Name) AS Item_Order_Count, -- 给聚合结果起别名，更清晰
        Customer_Sale.Quantity_Customer,
        Customer_Sale.Sale_Price,
        Customer_Sale.Total_Price,
        Customer_Sale.Date,
        Total_Remaining_Previous,
        Today_Credit,
        Total_Remaining_Now,
        Today_Receiving,
        Total_Xhot,
        -- 用窗口函数计算每个客户的总订单数
        SUM(COUNT(Item.Item_Name)) OVER (PARTITION BY Customer.Customer_Id) AS Total_Customer_Orders
    FROM Customer
    JOIN Customer_Sale ON Customer.Customer_Id = Customer_Sale.Customer_Id
    INNER JOIN Customer_Account ON Customer.Customer_Id = Customer_Account.Customer_Id
    INNER JOIN Unit ON Customer_Sale.Unit_Id = Unit.Unit_Id
    INNER JOIN Item ON Customer_Sale.Item_Id = Item.Item_Id
    WHERE Customer_Sale.Date = @date AND Customer_Account.Customer_Date = @date
    -- 修正GROUP BY：包含所有SELECT中的非聚合字段
    GROUP BY 
        Customer.Customer_Id, Customer.Customer_Name,
        Item.Item_Id, Item.Item_Name,
        Customer_Sale.Quantity_Customer,
        Customer_Sale.Sale_Price,
        Customer_Sale.Total_Price,
        Customer_Sale.Date,
        Total_Remaining_Previous,
        Today_Credit,
        Total_Remaining_Now,
        Today_Receiving,
        Total_Xhot
    -- 按客户总订单数降序，再按单商品订单数降序
    ORDER BY Total_Customer_Orders DESC, Item_Order_Count DESC;
END

关键修改点说明 #

• 别名优化：给COUNT(Item.Item_Name)起了Item_Order_Count的别名，让结果更易读；
• 窗口函数计算总订单数：用SUM(COUNT(Item.Item_Name)) OVER (PARTITION BY Customer.Customer_Id)计算每个客户的所有商品订单数总和，这样就能按这个总和排序；
• 修正GROUP BY：把所有SELECT中的非聚合字段都加入GROUP BY，避免SQL语法错误（比如MySQL的ONLY_FULL_GROUP_BY模式下会报错）；
• 排序规则：ORDER BY Total_Customer_Orders DESC确保订单总量最多的客户排在最前面，后面的Item_Order_Count DESC让同一客户下订单多的商品也排在前面。

如果你只想按单商品的订单数排序（而不是客户总订单数），那只需要把ORDER BY改成ORDER BY Item_Order_Count DESC即可，但根据你的描述，前者应该是你需要的效果。

内容的提问来源于stack exchange，提问作者Muhammad Naveed