用于比较产品销售额的SQL查询
我有一个每月状态数据库视图我需要基于的生成报表。在视图中的数据看起来是这样的：用于比较产品销售额的SQL查询
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年有销售，并且仍然出现。
此外，该报告实际上是对财政年度 - 所以我很想有两种，如果有在说5月份为2007年任或2008
我看起来查询没有销售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这个查询，所以如果有任何有用的新功能，可能会帮助我让我知道。
case语句是我最好的朋友的SQL。你还需要一个时间表来在两个月内生成0转。
假设是基于以下各表的可用性：
销售：分类|收入| Yearh | 月
和
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具有枢纽的语法，会做这一点。
这里有一些额外的资源。 CASE http://www.4guysfromrolla.com/webtech/102704-1.shtml SQL SERVER 2005 PIVOT http://msdn.microsoft.com/en-us/library/ms177410.aspx
我可能是错的，但不应该使用一个完整的外连接而不是一个左连接？这样你会从这两个表中获得'空'列。
@Christian - 降价编辑 - 消化道出血;尤其是当预览和最终版本的帖子不同意时... @Christian - 完全外部联接 - 由于在WHERE子句中引用了SP1，并且WHERE子句是在JOIN之后应用。要在其中一个表上执行完整的外连接并对其进行筛选，需要将WHERE子句放入子查询中，因此在连接前发生之间的过滤，或尝试将所有WHERE条件构建到JOIN ON子句上，这是非常丑陋的。那么，实际上没有办法做到这一点。
@Jonas：考虑到这一点：
此外，该报告实际上是对财政年度 - 所以我很想有两种，如果有在说5个月没有销售与0空列，列2007年或2008年。
，事实上，这项工作不能用一个漂亮的查询做，我肯定会尽力给你真正想要的结果。毫无疑问，您的查询很难，甚至没有得到您真正想要的确切数据。;）
因此，我建议在5个步骤执行此操作：
1.创造你想要的格式的临时表的结果相匹配
2.填充它与十二行，用1-12本月列
3.更新使用SP1的逻辑
4.更新“去年”一栏使用SP2的逻辑“今年”栏
5.从临时表中选择
当然，我猜测我正在假设你可以创建一个存储过程来完成这个工作。你可能在技术上能够直接运行这整个批次，但这种丑陋是很少见的。如果你不能创建一个SP，我建议你通过子查询回退完整的外连接，但是如果一个月每个月都没有销售，那么它不会让你连续。
关于降价 - 是啊，这是令人沮丧的。编辑并预览我的HTML表格，但张贴走后 - 所以不得不删除所有HTML从后格式化......
@kcrumley我认为我们已经达成了类似的结论。这个查询很容易变得很难看。实际上，在阅读您的答案之前，我已经使用类似的方法解决了这个问题（但方式不同）我有权在报告数据库上创建存储过程和函数。我创建了一个Table Valued函数，接受一个产品类别和一个财政年度作为参数。根据该函数将填充一个包含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功能是相当混乱！但至少它的工作原理 - 这让我很高兴现在;）
的窍门是做一个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年销售的行，或两者都有。要获得在任何一年都没有销售的行，您只需要INNER JOIN到1-12数字表（您做have one of those，是吗？）。
使用支点和动态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 ('[email protected]+')
) 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