使用来自一个DataTable的数据来获取另一个get的结果
使用来自一个DataTable的数据来获取另一个get的结果
提问于 2018-10-15 23:36:34
我目前有一个导出到DataTable (dt)的存储过程。提供的数据显示了每个客户每月的平均价值。数据必须显示它们的值，如果它们有值要显示的话。
下面是我的存储过程(动态透视表)的SQL：
ALTER Procedure [dbo].[stpMonthlyAvg]
--Parameters
AS
Begin
Begin Tran T1;
Begin Try
DECLARE @Dates NVARCHAR(MAX);
SELECT @Dates = CONCAT(@Dates + ', ', QUOTENAME(BalMonth))
FROM vAvgMonBal
GROUP BY BalMonth
ORDER BY BalMonth;
DECLARE @DynSQL NVARCHAR(MAX),
@months NVARCHAR(255);
SET @months = 'CONCAT( CONVERT(nvarchar(15), YEAR(BalDate)) , IIF(LEN(MONTH(BalDate)) > 1, CONVERT(nvarchar(15), MONTH(BalDate)), ''0'' + CONVERT(nvarchar(15), MONTH(BalDate))))'
SET @DynSQL = 'SELECT *
FROM
(SELECT
a1.IDNbr,
a2.CustName, ' + @months + ' AS BalMonth,
AVG(a1.Balance) as Balance
FROM tblID a1
INNER JOIN tblCust a2 ON (a1.IDNbr = a2.IDNbr)
WHERE a2.CustType != ''Inactive'' AND a2.CustType IS NOT NULL AND a1.Balance != 0
GROUP BY
a1.IDNbr, a2.CustName, ' + @months +') as d1
PIVOT (
AVG(Balance)
FOR BalMonth IN (' + @Dates + ')
) piv';
EXECUTE sp_executesql @DynSQL
Commit Tran T1;
End Try
Begin Catch
RollBack Tran T1;
End Catch
End下面是我当前拥有的表的一个示例--带有测试数据：
+-------------------------------------------------------+
| ID | CustName | 201501 | 201502 | 201503 | 201504 | ..|
+-------------------------------------------------------+
| 32 | CustOne | 100.00 | 200.00 | 400.00 | 700.00 | ..|
| 56 | CustTwo | 350.00 | 375.00 | 400.00 | 500.00 | ..|
| 89 | CustThree| 222.22 | 333.33 | 444.44 | 555.55 | ..|
| .. | ... | .. | .. | .. | .. | ..|
+-------------------------------------------------------+我需要找出每个客户每个月与前一个月之间的百分比差异，并将其添加到自己的DataTable(fDt)中。
下面是我需要的(行中的值是百分比)：
+-------------------------------------------------------+
| ID | CustName | PerDiff02 | PerDiff03 | PerDiff04 | ..|
+-------------------------------------------------------+
| 32 | CustOne | 0 | 100 | 200 | ..|
| 56 | CustTwo | 93.33 | 93.75 | 80.00 | ..|
| 89 | CustThree| 66.66 | 75.00 | 80.00 | ..|
| .. | ... | .... | .... | .... | ..|
+-------------------------------------------------------+我目前已经将数据添加到fDt中，不过它是按列计算的，而不是按行计算的(我想)。
这意味着我的数据输出如下所示：
+-------------------------------------------------------+
| ID | CustName | PerDiff02 | PerDiff03 | PerDiff04 | ..|
+-------------------------------------------------------+
| 32 | CustOne | 0 | 100 | 200 | ..|
| 56 | CustTwo | 0 | 100 | 200 | ..|
| 89 | CustThree| 0 | 100 | 200 | ..|
| .. | ... | .... | .... | .... | ..|
+-------------------------------------------------------+因为它将一次计算的结果赋值给整个列。
下面是我使用的代码：
Uni.fileExport Export = new Uni.fileExport();
string conStr = "Data Source=" + ConfigurationManager.AppSettings["DataSource"] + "Initial Catalog=" + ConfigurationManager.AppSettings["InitialCatalog"] + "Integrated Security=True;";
var dt = new DataTable();
dt = Export.sqlToDTMonthlyAvg(conStr, stp);
var fDt = new DataTable();
int i;
int fieldCount = dt.Columns.Count;
int finalSize = (fieldCount) * 2;
string[] finalCol = new string[finalSize];
string[] colHeaders = new string[fieldCount];
for (i = 0; i < fieldCount; i++)
{
colHeaders[i] = dt.Columns[i].ToString();
}
fDt.Columns.Add(colHeaders[0]);
fDt.Columns.Add(colHeaders[1]);
for (i = 1; i < dt.Rows.Count; i++)
{
fDt.Rows.Add(dt.Rows[i][0], dt.Rows[i][1]);
}
/*
* This section is meant to try and get the percent difference from dt
* to fDt. Looking into doing this from the SQL data base rather than from the code
* in order to save processing at runTime. The main concern being the stored procedure
* taking longer than a couple minutes.
*/
// Gets the column headers for dataTable fDt
for (i = 2; i <= (dt.Columns.Count - 2); i++)
{
string colName = "PerDiff" + dt.Columns[i + 1];
fDt.Columns.Add(colName, typeof(decimal));
}
for (i = 2; i <= (dt.Columns.Count - 2); i++)
{
var month1 = dt.Columns[i];
var month2 = dt.Columns[i + 1];
fDt.Columns[i].Expression = $"({month1} / {month2}) * 100";
}问题：如何才能得到dt到fDt列的百分比差异？我需要基于上个月的百分比。
(PerDiff从PerDiff02开始，因为在201501之前没有任何东西可以比较。)
如果有什么不清楚的地方，请告诉我，我会尽力澄清的！对于C#，尤其是datatables，我还是个新手。
我尝试过在SQL中实现这一点，但是我仍然是SQL的新手，我认为通过C#可以更容易地实现这一点。一旦我让它工作，然后优化将被考虑。
如果有任何反对票，我要求你解释为什么你是反对票，以便我可以纠正它！
回答 2
Stack Overflow用户
发布于 2018-10-16 00:26:38
好的，这是我两年前做的类似的事情
在内部选择中，添加占位符的差异
SELECT
a1.IDNbr,
a2.CustName, ' + @months + ' AS BalMonth,
AVG(a1.Balance) as Balance,
0 as Diff1,0 as Diff2,0 as Diff3在执行sp_executesql @DynSQL add之前
DECLARE @DataProcess TABLE // Table variable to hold everything
(
NewTableID INT IDENTITY(1,1)
, Id INT
, CustName NVARCHAR(256)
, Month1 NVARCHAR(260)
, Month2 NVARCHAR(260)
, Month3 NVARCHAR(260)
, Month4 NVARCHAR(260)
,Diff1 INT
,Diff2 INT
,Diff3 INT
)
INSERT INTO @DataProcess EXECUTE sp_executesql @DynSQL // fill in the data
Now declare values and loop
DECLARE @Count INT, @Counter INT, @Id INT,@Month1 NVARCHAR(260),@Month1 NVARCHAR(260),@Month3 NVARCHAR(260),@Month4 NVARCHAR(260)
SELECT @Count = COUNT(*) FROM @DataProcess d
SET @Counter = 1
BEGIN
SELECT @Id = ID,(260),@Month1 = Month1,@Month2=Month1 ,@Month3=Month3 ,@Month4 =Month3 FROM @DataProcess m WHERE NewTableID = Counter
m.Diff1 =@Month2 - @Month1;
m.Diff2 =@Month3 - @Month2
m.Diff2 =@Month4 - @Month3
SET @Counter = @Counter + 1
END 这应该会为您填满所有内容，而不会破坏使用sp
Stack Overflow用户
发布于 2018-10-16 03:41:15
这里的每个请求是我将在存储过程中完全执行的操作
Begin Tran T1;
Begin Try
DECLARE @Dates NVARCHAR(MAX);
SELECT @Dates = CONCAT(@Dates + ', ', QUOTENAME(BalMonth))
FROM vAvgMonBal
GROUP BY BalMonth
ORDER BY BalMonth;
DECLARE @DynSQL NVARCHAR(MAX),
@months NVARCHAR(255);
SET @months = 'CONCAT( CONVERT(nvarchar(15), YEAR(BalDate)) , IIF(LEN(MONTH(BalDate)) > 1, CONVERT(nvarchar(15), MONTH(BalDate)), ''0'' + CONVERT(nvarchar(15), MONTH(BalDate))))'
SET @DynSQL = 'SELECT *
FROM
(SELECT
a1.IDNbr,
a2.CustName, ' + @months + ' AS BalMonth,
AVG(a1.Balance) as Balance,
0 as Diff1,0 as Diff2,0 as Diff3
FROM tblID a1
INNER JOIN tblCust a2 ON (a1.IDNbr = a2.IDNbr)
WHERE a2.CustType != ''Inactive'' AND a2.CustType IS NOT NULL AND a1.Balance != 0
GROUP BY
a1.IDNbr, a2.CustName, ' + @month +') as d1
PIVOT (
AVG(Balance)
FOR BalMonth IN (' + @Dates + ')
) piv';
DECLARE @DataProcess TABLE // Table variable to hold everything
(
NewTableID INT IDENTITY(1,1)
, Id INT
, CustName NVARCHAR(256)
, Month1 NVARCHAR(260)
, Month2 NVARCHAR(260)
, Month3 NVARCHAR(260)
, Month4 NVARCHAR(260)
,Diff1 INT
,Diff2 INT
,Diff3 INT
)
INSERT INTO @DataProcess EXECUTE sp_executesql @DynSQL // fill in the data
Now declare values and loop
DECLARE @Count INT, @Counter INT, @Id INT,@Month1 NVARCHAR(260),@Month1 NVARCHAR(260),@Month3 NVARCHAR(260),@Month4 NVARCHAR(260)
SELECT @Count = COUNT(*) FROM @DataProcess d
SET @Counter = 1
BEGIN
SELECT @Id = ID,(260),@Month1 = Month1,@Month2=Month1 ,@Month3=Month3 ,@Month4 =Month3 FROM @DataProcess m WHERE NewTableID = Counter
m.Diff1 =@Month2 - @Month1;
m.Diff2 =@Month3 - @Month2
m.Diff2 =@Month4 - @Month3
SET @Counter = @Counter + 1
END
select * from @DataProcess // this reads all the data in the Temp Table and this is what fills your data table in .NEt
Commit Tran T1;
End Try
Begin Catch
RollBack Tran T1;
End Catch
End页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52820190
复制相关文章
相似问题
腾讯云开发者
