SQL查询按月比较产品销售

Question

我有一个“月度状态”数据库视图，我需要根据此视图生成报告。视图中的数据如下所示：

Category | Revenue  |  Yearh  |  Month
Bikes      10 000      2008        1
Bikes      12 000      2008        2
Bikes      12 000      2008        3
Bikes      15 000      2008        1
Bikes      11 000      2007        2
Bikes      11 500      2007        3
Bikes      15 400      2007        4

…等等

该视图具有产品类别，收入，一年零一个月。我想创建一个比较2007年和2008年的报告，在没有销售的月份中显示0。因此，报告应如下所示：

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400

要注意的关键是，第1个月在2008年仅有销售，因此2007年为0。此外，第4个月在2008年没有销售，因此0则在2007年有销售并且仍然显示。

另外，该报告实际上是针对财务年度的-因此，如果2007年或2008年的第5个月没有销售，那么我希望两个字段都为0。

我得到的查询看起来像这样：

SELECT 
    SP1.Program,
    SP1.Year,
    SP1.Month,
    SP1.TotalRevenue,
    IsNull(SP2.TotalRevenue, 0) AS LastYearTotalRevenue

FROM PVMonthlyStatusReport AS SP1 
     LEFT OUTER JOIN PVMonthlyStatusReport AS SP2 ON 
                SP1.Program = SP2.Program AND 
                SP2.Year = SP1.Year - 1 AND 
                SP1.Month = SP2.Month
WHERE 
    SP1.Program = 'Bikes' AND
    SP1.Category = @Category AND 
    (SP1.Year >= @FinancialYear AND SP1.Year <= @FinancialYear + 1) AND
    ((SP1.Year = @FinancialYear AND SP1.Month > 6) OR 
     (SP1.Year = @FinancialYear + 1 AND SP1.Month <= 6))

ORDER BY SP1.Year, SP1.Month

此查询的问题在于，由于我们在2008年没有任何销售，但实际上在2007年有销售，因此它不会返回上述示例数据中的第四行。

这可能是一个常见的查询/问题，但是经过很长时间的前端开发后，我的SQL还是很生锈。任何帮助是极大的赞赏！

哦，顺便说一句，我正在使用SQL 2005进行此查询，因此，如果有任何有用的新功能可能会让我知道。

Answer 1

Case Statement是我最好的sql朋友。您还需要一个表来确定两个月中产生0转速的时间。

假设基于下表的可用性：

销售：类别| 收入| 年 月

和

tm：年| 月（填入报告所需的所有日期）

示例1没有空行：

select
    Category
    ,month
    ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
    ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year

from
    sales

where
    year in (2008,2007)

group by
    Category
    ,month

退货：

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400

带有空行的示例2：我将使用子查询（但其他查询可能不会），并且将为每个产品和年份月份组合返回一个空行。

select
    fill.Category
    ,fill.month
    ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
    ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year

from
    sales
    Right join (select distinct  --try out left, right and cross joins to test results.
                   product
                   ,year
                   ,month
               from
                  sales --this ideally would be from a products table
                  cross join tm
               where
                    year in (2008,2007)) fill


where
    fill.year in (2008,2007)

group by
    fill.Category
    ,fill.month

退货：

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400
Bikes          5          0                    0
Bikes          6          0                    0
Bikes          7          0                    0
Bikes          8          0                    0

请注意，大多数报告工具都将执行此交叉表或矩阵功能，现在我想到了，SQL Server 2005具有也可以执行此操作的数据透视语法。

这里是一些其他资源。案例
http://www.4guysfromrolla.com/webtech/102704-1.shtml
SQL Server 2005 PIVOT http://msdn.microsoft.com/zh-
cn/library/ms177410.aspx