SQL查询，按月比较产品销售情况

我可能是错的，但你不应该使用完整的外部连接而不仅仅是左连接吗？这样，您将从两个表中获得“空”列。

http://en.wikipedia.org/wiki/Join_（SQL）#Full_outer_join

@Christian -- markdown 编辑器 -- 呃;尤其是当您的帖子的预览和最终版本不一致时...... @Christian -- 完全外部连接 -- 由于 WHERE 子句中引用了 SP1，并且 WHERE 子句在 JOIN 之后应用，因此完全外部连接被否决。要对其中一个表进行完整的外部连接和过滤，您需要将 WHERE 子句放入子查询中，以便过滤在连接之前进行，或者尝试将所有 WHERE 条件构建到 JOIN ON 子句上，这非常丑陋。好吧，实际上没有很好的方法可以做到这一点。

@Jonas：考虑到这一点：

此外，该报告实际上是针对财政年度的 - 因此，如果 2007 年或 2008 年的第 5 个月没有销售额，我希望两者都有 0 的空列。

事实上，这项工作无法通过漂亮的查询来完成，我肯定会尝试获得您真正想要的结果。有一个丑陋的查询，甚至没有得到你真正想要的确切数据是没有意义的。;)

因此，我建议分 5 个步骤执行此操作：
1. 以您希望结果匹配
的格式创建一个临时表 2.用 12 行填充它，月份第 3 列
中有 1-12。使用 SP1 逻辑
更新“今年”列 4.使用 SP2 逻辑
更新“去年”列 5.从临时表中选择

当然，我想我是在假设您可以创建一个存储过程来实现这一点的基础上工作的。从技术上讲，您可能能够内联运行整个批处理，但这种丑陋很少见。如果你不能做一个 SP，我建议你通过子查询回退到完整的外部连接，但当一个月没有销售时，它不会给你一行。

关于降价 - 是的，这很令人沮丧。编辑器确实预览了我的 HTML 表格，但在发布后它就消失了 - 所以不得不从帖子中删除所有 HTML 格式......

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

然后，我将函数返回的两个表联接起来。由于我知道所有表格都有 12 个 roves，因此分配起来更容易，我可以加入产品类别和月份：

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 转的时间。

假设基于下表的可用性：

销售： 类别 |收入 |年 | 月

和

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 具有也可以执行此操作的透视语法。

以下是一些其他资源。 案例 https://web.archive.org/web/20210728081626/https://www.4guysfromrolla.com/webtech/102704-1.shtml SQL SERVER 2005 PIVOT http://msdn.microsoft.com/en-us/library/ms177410.aspx

诀窍是执行 FULL JOIN，使用 ISNULL 从任一表中获取连接列。我通常将其包装到视图或派生表中，否则您还需要在 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 年，或两者兼而有之。要获得任何一年都没有销售的行，您只需要将内部 JOIN 连接到一个 1-12 数字表（您确实有其中之一，不是吗？

使用 pivot 和 Dynamic Sql，我们可以实现这个结果

SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP

;With cte(Category , Revenue  ,  Yearh  ,  [Month])
AS
(
SELECT 'Bikes', 10000, 2008,1 UNION ALL
SELECT 'Bikes', 12000, 2008,2 UNION ALL
SELECT 'Bikes', 12000, 2008,3 UNION ALL
SELECT 'Bikes', 15000, 2008,1 UNION ALL
SELECT 'Bikes', 11000, 2007,2 UNION ALL
SELECT 'Bikes', 11500, 2007,3 UNION ALL
SELECT 'Bikes', 15400, 2007,4
)
SELECT * INTO #Temp FROM cte

Declare @Column nvarchar(max),
        @Column2 nvarchar(max),
        @Sql nvarchar(max)


SELECT @Column=STUFF((SELECT DISTINCT ','+ 'ISNULL('+QUOTENAME(CAST(Yearh AS VArchar(10)))+','+'''0'''+')'+ 'AS '+ QUOTENAME(CAST(Yearh AS VArchar(10)))
FROM #Temp order by 1 desc FOR XML PATH ('')),1,1,'')

SELECT @Column2=STUFF((SELECT DISTINCT ','+ QUOTENAME(CAST(Yearh AS VArchar(10)))
FROM #Temp FOR XML PATH ('')),1,1,'')

SET @Sql= N'SELECT Category,[Month],'+ @Column +'FRom #Temp
            PIVOT
            (MIN(Revenue) FOR yearh IN ('+@Column2+')
            ) AS Pvt

            '
EXEC(@Sql)
Print @Sql

结果

Category    Month   2008    2007
----------------------------------
Bikes       1       10000   0
Bikes       2       12000   11000
Bikes       3       12000   11500
Bikes       4       0       15400