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

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进行此查询，因此，如果有任何有用的新功能可能会让我知道。

解决方案

回答

我可能是错的，但我们不应该使用完整的外部联接而不是仅使用左联接吗？这样，我们将从两个表中获取"空"列。

http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join

回答

@Christian -markdown编辑器-UGH;特别是当帖子的预览和最终版本不一致时...
@Christian-完全外部连接-完全外部连接因以下事实而被否决：在WHERE子句中有对SP1的引用，并且在JOIN之后应用了WHERE子句。要对其中一个表进行过滤以进行完全外部联接，我们需要将WHERE子句放入子查询中，以便在联接之前进行过滤，或者尝试将所有WHERE条件构建到JOIN ON子句上，即疯狂的丑陋。好吧，实际上没有漂亮的方法可以做到这一点。

@Jonas：考虑到这一点：

Also, the report is actually for financial year - so I would love to have empty columns with 0 in both if there was no sales in say month 5 for either 2007 or 2008.

以及这项工作无法通过漂亮的查询完成的事实，我肯定会尝试获得我们真正想要的结果。进行丑陋的查询甚至没有获得我们真正想要的确切数据都没有意义。 ;)

因此，我建议分5个步骤进行操作：
1.以我们希望结果匹配的格式创建一个临时表
2.填充十二行，在月份列中输入1-12
3.使用SP1逻辑更新"今年"列
4.使用SP2逻辑更新"去年"列
5.从临时表中选择

当然，我想我是基于这样的假设进行工作的：我们可以创建一个存储过程来完成此任务。从技术上来说，我们也许可以在线运行整个批处理，但是这种丑陋很少见。如果我们无法制作SP，建议我们通过子查询退回完整的外部联接，但是当一个月的一年都没有销售时，它不会让我们连续。

回答

关于减价是令人沮丧的。编辑器确实预览了我的HTML表格，但在发布后消失了，因此必须从帖子中删除所有HTML格式...

@kcrumley我认为我们已经得出类似的结论。这个查询很容易变得丑陋。在阅读答案之前，我实际上使用类似(但又不同的方法)解决了这一问题。我有权在报表数据库上创建存储过程和函数。我创建了一个表值函数，该表值函数接受一个产品类别和一个财务年度作为参数。基于此，该函数将填充一个包含12行的表。如果有任何可用的销售，将用视图中的数据填充行，否则，行将具有0值。

然后，我将函数返回的两个表连接在一起。由于我知道所有表都有十二个粗纱，因此分配起来比较容易，我可以加入"产品类别"和"月份"：

SELECT 
    SP1.Program,
    SP1.Year,
    SP1.Month,
    SP1.TotalRevenue AS ThisYearRevenue,
    SP2.TotalRevenue AS LastYearRevenue
FROM GetFinancialYear(@Category, 'First Look',  2008) AS SP1 
     RIGHT JOIN GetFinancialYear(@Category, 'First Look',  2007) AS SP2 ON 
         SP1.Program = SP2.Program AND 
         SP1.Month = SP2.Month

我认为方法可能更简洁一些，因为GetFinancialYear函数非常混乱！但是至少它起作用了，这让我暂时感到高兴;)

回答

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

假设基于下表的可用性：

sales: Category | Revenue  |  Yearh  |
  Month

和

tm: Year | Month  (populated with all
  dates required for reporting)

示例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/en-us/library/ms177410.aspx

回答

诀窍是使用ISNULL进行FULL JOIN，以从任一表中获取联接的列。我通常将其包装到视图或者派生表中，否则我们还需要在WHERE子句中使用ISNULL。

SELECT 
    Program,
    Month,
    ThisYearTotalRevenue,
    PriorYearTotalRevenue
FROM (
    SELECT 
        ISNULL(ThisYear.Program, PriorYear.Program) as Program,
        ISNULL(ThisYear.Month, PriorYear.Month),
        ISNULL(ThisYear.TotalRevenue, 0) as ThisYearTotalRevenue,
        ISNULL(PriorYear.TotalRevenue, 0) as PriorYearTotalRevenue
    FROM (
        SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue 
        FROM PVMonthlyStatusReport 
        WHERE Year = @FinancialYear 
        GROUP BY Program, Month
    ) as ThisYear 
    FULL OUTER JOIN (
        SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue 
        FROM PVMonthlyStatusReport 
        WHERE Year = (@FinancialYear - 1) 
        GROUP BY Program, Month
    ) as PriorYear ON
        ThisYear.Program = PriorYear.Program
        AND ThisYear.Month = PriorYear.Month
) as Revenue
WHERE 
    Program = 'Bikes'
ORDER BY 
    Month

这样一来，最低需求行将在2007年或者2008年(或者同时出现在两者中)一起销售。要获得任一年都没有销售的行，我们只需要INNER JOIN到1到12的数字表即可(我们确实有其中一个，不是吗？)。