SQL 每月销售额前 10 名 [英] SQL Top 10 Sales Every Month

问题描述

大家好.我有一个 SQL 2008 express 数据库，名称为 tbl_Merchant，类似如下:

Greeting all. I have a SQL 2008 express database, lets name is tbl_Merchant, similar as following:

Merchant | Sales | Month
Comp.1      100     1
Comp.2      230     1
Comp.3      120     1
Comp.1      200     2
Comp.2      130     2
Comp.3      240     2
Comp.1      250     3
  .          .      .
  .          .      .
  .          .      .

我需要找出 12 个月内每个月销售额前 10 位的商家.

I need to find the top 10 merchant with sales every month over 12 months.

只要一个月就很容易了.

It is very easy if it is just one month.

SELECT TOP 10 
       Merchant, 
       Sales, 
       Month
FROM   tbl_Merchant
WHERE  Month = 1
ORDER BY Sales DESC

但是如果我想在 12 个月内找到它们，我就会陷入困境.我需要显示 120 个商家，它们是每个月的前 10 个销售商家.目前我的解决方案是将第 1 个月到第 12 个月的 12 个表合并在一起，但我认为这不是一个好方法.

But I am stuck if I wan to find them over 12 months. I need to display 120 merchants, which are top 10 sales merchant of each month. Currently my solution is to union 12 tables from month 1 to 12 together but I don't think it is a good way.

有人可以给我任何建议吗?

Can any one kindly give me any suggestion?

非常感谢.

推荐答案

适用于 Sql Server 2005+

Valid for Sql Server 2005+

试试这个整体:

SELECT TOP 10 
       Merchant, 
       SUM(Sales) Sales
FROM   tbl_Merchant
WHERE  Month BETWEEN 1 and 12
GROUP BY Merchant
ORDER BY 2 DESC

或

如果您需要每月前 10 个PER MONTH的详细信息

if you need details per month for top 10 PER MONTH

;WITH MonthsCTE(m) as
(
    SELECT 1 m
    UNION ALL 
    SELECT m+1
    FROM MonthsCTE
    WHERE m < 12
)
SELECT m [Month], t.*
FROM MonthsCTE
CROSS APPLY 
(
    SELECT TOP 10
       Merchant, 
       SUM(Sales) Sales
    FROM   tbl_Merchant
    WHERE  Month = MonthsCTE.m
    GROUP BY Merchant
    ORDER BY 2 DESC
) t

这篇关于SQL 每月销售额前 10 名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！