如何遍历存储过程中的表?

2022-01-23 00:00:00 subquery tsql sql-server sqlparameter sql-subselect

这个问题从 这个.

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

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:

客户类别日志:

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)

对于每个 Unit(在两个表中都有很多行，每个 MemberNo 一个，但在结果集中包含一行)，我需要填充四列:New、Assumed、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:

给定用户作为参数提供的月份和年份(例如1"代表月份(一月)和2016"代表年份)，找出每个 Unit 在 MonthlySales 中每个该月内的子类别(其中 BeginDate 小于或等于用户提供的月/年日期并且 EndDate 大于或等于提供的月/年日期).

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

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，我最好的(伪sql)刺是:

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;

相关文章