怎么遍历存储过程中的表?

本教程将介绍如何遍历存储过程中的表?的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

怎么遍历存储过程中的表? 教程 第1张

问题描述

这个问题从 这个.

我有两张表需要查询并从中收集一些计算得出的总和;我需要一个基于单位的结果集——每个单位一行,为它们计算的数据折叠到该行中.

这两个表包含以下相关成员:

客户类别日志:

Unit  varchar(25)
MemberNo varchar(10)
Category varchar(50)
Subcategory varchar(50)
BeginDateDatetime
EndDate  Datetime

ReportingMonthlySales:

Unit (VarChar)
MemberNo (VarChar)
MonthlySales (Money)
CYear (Int)
Cmonth (Int)

对于每个 Unit(在两个表中都有很多行,每个 MemberNo 一个,但在结果集中包含一行),我需要填充四列:New、Assumed、Existing 和 Organic.这些值是基于属于相应子类别"字段的单元的所有成员的总和.CustomerCategoryLog 表的 BeginDate/EndDate 值用于确定单位/成员在被评估的月/年期间属于哪个子类别.

所以结果集的简化形式如下所示:

Unit New  Assumed  Existing Organic  Total
---- ---  -------  -------- -------  -----
Abuelos $22  $44$33$11$110
Gramps  $12  $23$1 $34$70
. . .

用英语来表达这个问题是这样的:

所以看来我需要从年/月参数中创建一个日期,以便与 CustomerCategoryLog 表中的 BeginDate 和 EndDate 值进行比较(另一种选择是更改 CustomerCategoryLog 表,使其具有 BeginDateMonth,BeginDateYear、EndDateMonth 和 EndDateYear 整数;但我认为必须有一种直接的方法来根据提供的年/月参数创建日期.

我的问题是怎么在 TSQL 中实际构建它.我不是 SQLhead,我最好的(伪sql)刺是:

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 joinCustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND RMS.CYear = @Year and RMS.CMonth = @MonthAND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'New'),

MonthlySales as 'AssumedSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left joinCustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND  RMS.CYear = @Year and RMS.CMonth = @MonthAND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Assumed'),

MonthlySales as 'ExistingSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left joinCustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND  RMS.CYear = @Year and RMS.CMonth = @MonthAND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Existing'),

MonthlySales as 'OrganicSales' FROM (SELECT MonthlySales FROM ReportingMonthlySales RMS left joinCustomerCategoryLog CCL on RMS.Unit = CCL.Unit WHERE RMS.Unit = Unit AND  RMS.CYear = @Year and RMS.CMonth = @MonthAND CCL.BeginDate >= PARAMDATE AND CCL.EndDate <= PARAMDATE AND CCL.Subcategory = 'Organic')
FROM ReportingMonthlySales RMS2
ORDER BY RMS2.Unit

END WHILENOTEOF

我知道这不太正确,甚至可能不太正确,但希望对于 SQL 专家/jr 来说已经足够清楚了.读心者,以了解我需要做什么/正在尝试做什么.

以下是查询的两个表中的一些示例数据:

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

推荐答案

这看起来像是很长的路要走.

这样的事情怎么样?

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;

好了关于怎么遍历存储过程中的表?的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。