如何在存储过程中的表之间循环? [英] How can I loop through a table within a stored procedure?
问题描述
此问题从我有两个表需要查询并从中收集一些计算得出的总和；我需要一个基于单位的结果集-每个单位一行，并将它们的计算数据折叠到该行中.
I have two tables that I need to query and glean some calculated sums from; I need a result set based on units -- one row for each Unit, with calculated data for them folded into that row.
这两个表包含以下相关成员:
The two tables contain the following pertinent members:
CustomerCategoryLog :
Unit varchar(25)
MemberNo varchar(10)
Category varchar(50)
Subcategory varchar(50)
BeginDate Datetime
EndDate Datetime
ReportingMonthlySales :
Unit (VarChar)
MemberNo (VarChar)
MonthlySales (Money)
CYear (Int)
Cmonth (Int)
对于每个单元(两个表中都有很多行，每个MemberNo都有一个，但结果集中只包含一行)，我需要填充四列:New，Assumped，Existing和Organic.这些值是基于属于相应子类别"字段的成员的单位所有成员的总和. CustomerCategoryLog表的BeginDate/EndDate值用于确定在评估的月份/年份中单位/会员所属的子类别.
For each Unit (which has many rows in both tables, one for each MemberNo, but comprises a single row in the result set), I need to populate four columns: New, Assumed, Existing, and Organic. These values are the sums of all Members for the Unit based on those belonging to the corresponding "Subcategory" field. The CustomerCategoryLog table's BeginDate/EndDate values are used for determining what Subcategory the Unit/Member was a part of during the month/year being evaluated.
因此，结果集的简化形式如下:
So a simplified form of the result set looks like this:
Unit New Assumed Existing Organic Total
---- --- ------- -------- ------- -----
Abuelos $22 $44 $33 $11 $110
Gramps $12 $23 $1 $34 $70
. . .
用英语来表达问题，就像这样:
To put the problem in English, it's something like:
给定用户提供的参数的月份和年份(例如，"Month(一月)"为"1"，对于年份为"2016")，请找出每个单位在MonthlySales中有多少美元该月内的子类别(BeginDate小于或等于用户提供的月/年日期，而EndDate大于或等于提供的月/年日期).
因此，似乎需要从Year/Month参数创建Date以便与CustomerCategoryLog表中的BeginDate和EndDate值进行比较(另一种选择是更改CustomerCategoryLog表，使其具有BeginDateMonth， BeginDateYear，EndDateMonth和EndDateYear整数；但我认为必须有一种从提供的Year/Month参数创建日期的简单方法.
So it seems that I will need to create a Date from the Year/Month parameters for comparison with the BeginDate and EndDate values in the CustomerCategoryLog table (The other option would be to alter the CustomerCategoryLog table, so that it has BeginDateMonth, BeginDateYear, EndDateMonth, and EndDateYear ints; but I reckon there must be a somewhat straightforward way to create a date from the Year/Month parameters supplied).
我的问题是如何在TSQL中实际构造它.我不是SQLhead，对此我最好的(pseudosql)刺是:
My problem is how to actually construct that in TSQL. I'm not a SQLhead, and my best (pseudosql) stab at it is:
DECLARE @Unit varchar(30);
DECLARE @Year Int;
DECLARE @Month Int;
. . .
DECLARE @PARAMDATE DATETIME = (Year + Month + 01).ToDateTime();
SELECT DISTINCT UNIT INTO #UNITS U FROM ReportingMonthlySales
WHILE NOT U.EOF DO
Unit = U.Unit
SELECT Unit, MonthlySales as 'NewSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left join
CustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND RMS.CYear = @Year and RMS.CMonth = @Month
AND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'New'),
MonthlySales as 'AssumedSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left join
CustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND RMS.CYear = @Year and RMS.CMonth = @Month
AND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Assumed'),
MonthlySales as 'ExistingSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left join
CustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND RMS.CYear = @Year and RMS.CMonth = @Month
AND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Existing'),
MonthlySales as 'OrganicSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left join
CustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND RMS.CYear = @Year and RMS.CMonth = @Month
AND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Organic')
FROM ReportingMonthlySales RMS2
ORDER BY RMS2.Unit
END WHILENOTEOF
我知道这不太正确，甚至可能不是很正确，但是希望对于SQL专家/jr来说已经足够清楚了.介意的读者了解我需要做的/我想做的.
I know this is not quite right, and probably not even remotely right, but hopefully it's clear enough for a SQL expert/jr. mind reader to understand what it is I need to do/am trying to do.
以下是从两个表中查询的一些示例数据:
Here's some sample data from the two tables that are queried:
CustomerCategoryLog表:
MemberNo = 007
Unit = AMC THEATERS
Subcategory = New
BeginDate = 1/1/2016
EndDate = 12/31/2016
MemberNo = 029
Unit = FOODBUY HMS
Subcategory = Existing
BeginDate = 1/1/2015
EndDate = 12/31/2015
ReportingMonthlySales表:
Unit = AMC THEATERS
MemberNo = 007
MonthlySales = $988.82
CYear = 2016
Cmonth = 1
Unit = FOODBUY HMS
MemberNo = 029
MonthlySales = $61,479.28
CYear = 2017
Cmonth = 3
推荐答案
这似乎是很长的路要走.
This just looks like the long way to pivot.
这样的事情怎么样?
declare @Unit varchar(30);
declare @Year int;
declare @Month int;
declare @paramdate datetime = datefromparts(@year, @month, 1);
/* --prior to sql server 2012
declare @paramdate datetime;
set @paramdate = convert(datetime,convert(char(4),@Year)
+right('0'+convert(varchar(2),@month),2)
+'01')
*/
select distinct unit
into #Units
from ReportingMonthlySales;
select
u.Unit
, New = sum(case when ccl.Subcategory = 'New' then rms.MonthlySales else 0 end)
, Assumed = sum(case when ccl.Subcategory = 'Assumed' then rms.MonthlySales else 0 end)
, Existing = sum(case when ccl.Subcategory = 'Existing' then rms.MonthlySales else 0 end)
, Organic = sum(case when ccl.Subcategory = 'Organic' then rms.MonthlySales else 0 end)
from #Units u
left join CustomerCategoryLog ccl
on u.Unit = ccl.Unit
and @paramdate >= ccl.begindate
and @paramdate <= ccl.enddate
left join ReportingMonthlySales rms
on u.Unit = rms.Unit
and rms.cyear = @year
and rms.cmonth = @month
group by u.unit;
这篇关于如何在存储过程中的表之间循环?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
