按月比较产品销售额的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to compare product sales by month
提问by Jonas Folles?
I have a Monthly Status database view I need to build a report based on. The data in the view looks something like this:
我有一个每月状态数据库视图，我需要基于它来构建报告。视图中的数据如下所示：
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
... And so forth
......等等
The view has a product category, a revenue, a year and a month. I want to create a report comparing 2007 and 2008, showing 0 for the months with no sales. So the report should look something like this:
该视图具有产品类别、收入、年份和月份。我想创建一个比较 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
The key thing to notice is how month 1 only has sales in 2008, and therefore is 0 for 2007. Also, month 4 only has no sales in 2008, hence the 0, while it has sales in 2007 and still show up.
要注意的关键是第 1 个月只有 2008 年的销售额，因此 2007 年为 0。此外，第 4 个月只有 2008 年没有销售额，因此为 0，而它在 2007 年有销售额并且仍然出现。
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.
此外，该报告实际上是针对财政年度的 - 因此，如果 2007 年或 2008 年的第 5 个月没有销售，我希望在两个空列中都有 0。
The query I got looks something like this:
我得到的查询看起来像这样：
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
The problem with this query is that it would not return the fourth row in my example data above, since we didn't have any sales in 2008, but we actually did in 2007.
这个查询的问题是它不会返回我上面示例数据中的第四行，因为我们在 2008 年没有任何销售额，但我们实际上在 2007 年有。
This is probably a common query/problem, but my SQL is rusty after doing front-end development for so long. Any help is greatly appreciated!
这可能是一个常见的查询/问题，但是我的 SQL 在做了这么长时间的前端开发后生锈了。任何帮助是极大的赞赏！
Oh, btw, I'm using SQL 2005 for this query so if there are any helpful new features that might help me let me know.
哦，顺便说一句，我正在使用 SQL 2005 进行此查询，所以如果有任何有用的新功能可以帮助我知道的话。
采纳答案by jason saldo
The Case Statement is my best sql friend. You also need a table for time to generate your 0 rev in both months.
Case 语句是我最好的 sql 朋友。您还需要一个时间表来在两个月内生成 0 转。
Assumptions are based on the availability of following tables:
假设基于下表的可用性：
sales: Category | Revenue | Yearh | Month
销售： 类别 | 收入 | 年 | 月
and
和
tm: Year | Month (populated with all dates required for reporting)
tm: 年 | 月（填充报告所需的所有日期）
Example 1 without empty rows:
没有空行的示例 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
RETURNS:
退货：
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
Example 2 with empty rows: I am going to use a sub query (but others may not) and will return an empty row for every product and year month combo.
带有空行的示例 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
RETURNS:
退货：
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
Note that most reporting tools will do this crosstab or matrix functionality, and now that i think of it SQL Server 2005 has pivot syntax that will do this as well.
请注意，大多数报告工具都会执行此交叉表或矩阵功能，现在我认为 SQL Server 2005 具有可以执行此操作的数据透视语法。
Here are some additional resources. CASE http://www.4guysfromrolla.com/webtech/102704-1.shtmlSQL SERVER 2005 PIVOT http://msdn.microsoft.com/en-us/library/ms177410.aspx
这里有一些额外的资源。案例 http://www.4guysfromrolla.com/webtech/102704-1.shtmlSQL SERVER 2005 PIVOT http://msdn.microsoft.com/en-us/library/ms177410.aspx
回答by Kevin Crumley
@Christian -- markdown editor -- UGH; especially when the preview and the final version of your post disagree... @Christian -- full outer join -- the full outer join is overruled by the fact that there are references to SP1 in the WHERE clause, and the WHERE clause is applied after the JOIN. To do a full outer join with filtering on one of the tables, you need to put your WHERE clause into a subquery, so the filtering happens beforethe join, or try to build all of your WHERE criteria onto the JOIN ON clause, which is insanely ugly. Well, there's actually no pretty way to do this one.
@Christian——降价编辑器——UGH；特别是当您的帖子的预览和最终版本不一致时...@Christian -- 全外联接 -- 全外联接被 WHERE 子句中对 SP1 的引用所否决，并且应用了 WHERE 子句加入后。要对其中一个表进行完全外部联接和过滤，您需要将 WHERE 子句放入子查询中，以便在联接之前进行过滤，或者尝试将所有 WHERE 条件构建到 JOIN ON 子句上，即丑的离谱。嗯，实际上没有很好的方法来做到这一点。
@Jonas: Considering this:
@乔纳斯：考虑到这一点：
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.
此外，该报告实际上是针对财政年度的 - 因此，如果 2007 年或 2008 年的第 5 个月没有销售，我希望在两个空列中都有 0。
and the fact that this job can't be done with a pretty query, I would definitely try to get the results you actually want. No point in having an ugly query and not even getting the exact data you actually want. ;)
事实上，这项工作无法通过漂亮的查询来完成，我肯定会尝试获得您真正想要的结果。进行丑陋的查询甚至没有获得您真正想要的确切数据毫无意义。;)
So, I'd suggest doing this in 5 steps:
1. create a temp table in the format you want your results to match
2. populate it with twelve rows, with 1-12 in the month column
3. update the "This Year" column using your SP1 logic
4. update the "Last Year" column using your SP2 logic
5. select from the temp table
因此，我建议分 5 个步骤执行此操作：
1. 以您希望结果匹配的格式创建一个临时表
2. 用 12 行填充它，在月份列中使用 1-12
3. 更新“今年" 列使用您的 SP1 逻辑
4.使用您的 SP2 逻辑更新“去年”列
5. 从临时表中选择
Of course, I guess I'm working from the assumption that you can create a stored procedure to accomplish this. You might technically be able to run this whole batch inline, but that kind of ugliness is very rarely seen. If you can't make an SP, I suggest you fall back on the full outer join via subquery, but it won't get you a row when a month had no sales either year.
当然，我想我的工作假设您可以创建一个存储过程来完成此操作。从技术上讲，您可能能够内联运行整个批处理，但这种丑陋很少见。如果您不能创建 SP，我建议您通过子查询使用完整的外部联接，但是当任何一年的一个月都没有销售时，它不会让您连续。
回答by Jonas Folles?
About the markdown - Yeah that is frustrating. The editor did preview my HTML table, but after posting it was gone - So had to remove all HTML formatting from the post...
关于降价 - 是的，这令人沮丧。编辑器确实预览了我的 HTML 表格，但发布后它不见了 - 所以不得不从帖子中删除所有 HTML 格式......
@kcrumley I think we've reached similar conclusions. This query easily gets real ugly. I actually solved this before reading your answer, using a similar (but yet different approach). I have access to create stored procedures and functions on the reporting database. I created a Table Valued function accepting a product category and a financial year as the parameter. Based on that the function will populate a table containing 12 rows. The rows will be populated with data from the view if any sales available, if not the row will have 0 values.
@kcrumley 我认为我们已经得出了类似的结论。这个查询很容易变得非常难看。在阅读您的答案之前，我实际上使用了类似（但不同的方法）解决了这个问题。我有权在报告数据库上创建存储过程和函数。我创建了一个表值函数，接受一个产品类别和一个财政年度作为参数。基于此，该函数将填充一个包含 12 行的表。如果有任何销售可用，这些行将使用视图中的数据填充，否则该行将具有 0 个值。
I then join the two tables returned by the functions. Since I know all tables will have twelve roves it's allot easier, and I can join on Product Category and Month:
然后我连接函数返回的两个表。因为我知道所有表都有 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
I think your approach is probably a little cleaner as the GetFinancialYear function is quite messy! But at least it works - which makes me happy for now ;)
我认为您的方法可能更简洁一些，因为 GetFinancialYear 函数非常混乱！但至少它有效 - 这让我现在很高兴;）
回答by Christian Hagelid
I could be wrong but shouldn't you be using a full outer join instead of just a left join? That way you will be getting 'empty' columns from both tables.
我可能是错的，但您不应该使用完整的外部联接而不仅仅是左联接吗？这样，您将从两个表中获得“空”列。
回答by Mark Brackett
The trick is to do a FULL JOIN, with ISNULL's to get the joined columns from either table. I usually wrap this into a view or derived table, otherwise you need to use ISNULL in the WHERE clause as well.
诀窍是做一个完全连接，用 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
That should get you your minimum requirements - rows with sales in either 2007 or 2008, or both. To get rows with no sales in either year, you just need to INNER JOIN to a 1-12 numbers table (you do have one of those, don't you?).
这应该可以满足您的最低要求 - 2007 年或 2008 年或两者都有销售额的行。要获得任何一年都没有销售额的行，您只需要 INNER JOIN 到 1-12 个数字表（您确实有其中一个，不是吗？）。
回答by Mark Brackett
Using pivot and Dynamic Sql we can achieve this result
使用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
Result
结果
Category Month 2008 2007
----------------------------------
Bikes 1 10000 0
Bikes 2 12000 11000
Bikes 3 12000 11500
Bikes 4 0 15400
