代码之家 › 专栏 › 技术社区 › Jonas Follesø

用于按月比较产品销售的SQL查询

reporting sql-server-2005 sql-server sql

Jonas Follesø · 技术社区 · 16 年前

我有一个每月的状态数据库视图,我需要建立一个基于它的报告。视图中的数据如下所示:

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

要注意的关键是第一个月在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进行这个查询,所以如果有任何有帮助的新特性可以帮助我,请告诉我。

6 回复 | 直到 7 年前

jason saldo 16 年前

case语句是我最好的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透视 http://msdn.microsoft.com/en-us/library/ms177410.aspx

ddimitrov 16 年前

@克里斯蒂安——降价编辑——啊,尤其是当你的文章的预览和最终版本不一致的时候…… @Christian——完全外部联接——完全外部联接被这样一个事实所推翻:在WHERE子句中有对SP1的引用,并且在联接之后应用WHERE子句。要对其中一个表进行完全外部联接和筛选,需要将WHERE子句放入子查询中,以便进行筛选。之前在join中,或者尝试在join on子句中构建所有的where条件,这是非常丑陋的。好吧,事实上没有什么好办法。

@乔纳斯:考虑到这一点:

另外,报告实际上是针对财政年度的——所以 如果在2007年或2008年的第5个月没有销售,我希望两个月都有0的空列。

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

因此,我建议分5个步骤进行:
1。以希望结果匹配的格式创建临时表
2。用12行填充它,在Month列中用1-12
三。使用SP1逻辑更新“今年”列
4。使用SP2逻辑更新“去年”列
5。从临时表中选择

当然,我想我是在假设您可以创建一个存储过程来实现这一点的基础上工作的。从技术上讲,您可能能够以内联方式运行整个批处理,但这种丑陋的情况很少出现。如果您不能创建一个SP,我建议您通过子查询重新进行完整的外部联接,但是当一个月的任何一年都没有销售额时,它不会给您带来任何影响。

Jonas Follesø 16 年前

关于减价-是的,这很令人沮丧。编辑器确实预览了我的HTML表,但是在发布之后,它就不见了——所以必须从发布中删除所有HTML格式…

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

然后,我连接函数返回的两个表。因为我知道所有的表都有12个rove,所以分配更容易,我可以加入产品类别和月份:

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

我认为你的方法可能更干净一点,因为财务分析功能相当混乱!但至少它起作用了——这让我现在很高兴;)

Mark Brackett Achilles Ram Nakirekanti 16 年前

诀窍是进行完全联接,使用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年的销售额或两者都有关系。要获取任何一年都没有销售的行,只需内部联接到1-12数字表(需要 have one of those 是吗?).

Jeff Atwood 16 年前

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

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

user7715598 7 年前

使用Pivot和动态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