我可以在 T-SQL 中使用 while 或游标插入到新表中吗?

Question

我写了一个查询来获取personID的每个日期的总和 我想做的是使用存储过程将结果插入到新表中，我的查询类似于参考(使用 AdventureWorks2019(

WITH TOP7 AS
(
SELECT 
SOH.SalesPersonID, SOH.OrderDate,
SUM(SOH.TotalDue) AS Total,
ROW_NUMBER() OVER (PARTITION BY SOH.SalesPersonID ORDER BY SOH.OrderDate DESC) AS RowNum
FROM 
Sales.SalesOrderHeader AS SOH
LEFT JOIN 
Sales.SalesOrderHeader AS SOH2 ON SOH.SalesOrderID = SOH2.SalesOrderID
WHERE 
SOH.SalesPersonID IS NOT NULL 
GROUP BY
SOH.OrderDate, SOH.SalesPersonID
)
(
SELECT SalesPersonID, OrderDate, Total, RowNum
FROM TOP7
WHERE RowNum <= 7
) 

这是我得到的输出：

ID    Date                        Total      RowNum
----------------------------------------------------
274   2014-05-01 00:00:00.000    42546.9235   1
274   2014-03-31 00:00:00.000   110623.7157   2
274   2014-03-01 00:00:00.000    46525.3068   3
274   2014-01-29 00:00:00.000     1592.5736   4
274   2013-11-30 00:00:00.000    79835.0276   5
274   2013-09-30 00:00:00.000   102227.2339   6
274   2013-08-30 00:00:00.000      2194.914   7
275   2014-05-01 00:00:00.000    417208.4727  1
275   2014-03-31 00:00:00.000    273730.5071  2
275   2014-03-01 00:00:00.000    221438.2289  3
275   2014-01-29 00:00:00.000    279451.2658  4

我想做的是将结果插入到一个新表中，如下所示

ID, Fdate, Ftotal, Sdate, Stotal, Tdate, Ttotal, Fdate, Ftotal, Fidate, Fitotal, Sidate, Sitotal, Sedate, Stotal
274 | 2014-05-01 | 42546.9235 | 2014-03-31 | 110623.7157 | 2014-03-01 | 46525.3068  | 2014-01-29 | 1592.5736 | 2013-11-30 | 79835.0276   | 2013-09-30 | 102227.2339 | 2013-08-30 | 2194.914 

我正在使用 SQL Server。

我想为结果中的所有 Id 执行此操作 - 我如何实现？

提前谢谢你

编辑：

我这样做是为了插入到新表中

WITH TOP7 AS
(
SELECT 
SOH.SalesPersonID, SOH.OrderDate,
SUM(SOH.TotalDue) AS Total,
ROW_NUMBER() OVER (PARTITION BY SOH.SalesPersonID ORDER BY SOH.OrderDate DESC) AS RowNum
FROM 
Sales.SalesOrderHeader AS SOH
LEFT JOIN 
Sales.SalesOrderHeader AS SOH2 ON SOH.SalesOrderID = SOH2.SalesOrderID
WHERE 
SOH.SalesPersonID IS NOT NULL 
GROUP BY
SOH.OrderDate, SOH.SalesPersonID
)
INSERT INTO [Person].[WeekEmployeeTotals] ([PersonID]
,[FirstDate]
,[FirstDateTotal]
,[SecondDate]
,[SecondDateTotal]
,[ThirdDate]
,[ThirdDateTotal]
,[FourthDate]
,[FourthDateTotal]
,[FifthDate]
,[FifthDateTotal]
,[SixthDate]
,[SixthDateTotal]
,[SeventhDate]
,[SeventhDateTotal])
SELECT  SalesPersonID, 
Date1 = MAX(CASE WHEN RowNum = 1 THEN OrderDate END),
Total1 = SUM(CASE WHEN RowNum = 1 THEN Total END),
Date2 = MAX(CASE WHEN RowNum = 2 THEN OrderDate END),
Total2 = SUM(CASE WHEN RowNum = 2 THEN Total END),
Date3 = MAX(CASE WHEN RowNum = 3 THEN OrderDate END),
Total3 = SUM(CASE WHEN RowNum = 3 THEN Total END),
Date4 = MAX(CASE WHEN RowNum = 4 THEN OrderDate END),
Total4 = SUM(CASE WHEN RowNum = 4 THEN Total END),
Date5 = MAX(CASE WHEN RowNum = 5 THEN OrderDate END),
Total5 = SUM(CASE WHEN RowNum = 5 THEN Total END),
Date6 = MAX(CASE WHEN RowNum = 6 THEN OrderDate END),
Total6 = SUM(CASE WHEN RowNum = 6 THEN Total END),
Date7 = MAX(CASE WHEN RowNum = 7 THEN OrderDate END),
Total7 = SUM(CASE WHEN RowNum = 7 THEN Total END)
FROM    TOP7
WHERE   RowNum <= 7
GROUP BY SalesPersonID;

但我有一个问题，如何设置选择要插入的内容，我的意思是这样的

Create PROCEDURE [dbo].[InsertFromSHO]
@PID INT,
@D1 datetime,
@T1 numeric(24,7),
@D2 datetime,
@T2 numeric(24,7),
@D3 datetime,
@T3 numeric(24,7),
@D4 datetime,
@T4 numeric(24,7),
@D5 datetime,
@T5 numeric(24,7),
@D6 datetime,
@T6 numeric(24,7),
@D7 datetime,
@T7 numeric(24,7)
AS
SET @D1 = (SELECT Date1 = MAX(CASE WHEN RowNum = 1 THEN OrderDate END) FROM TOP7 WHERE RowNum <= 7)

^ 我知道这是错误的，但这就是我要做的，因为我将来需要做很多事情，所以我需要知道一个好方法来实现这一目标

Answer 1

可以使用条件聚合在所需的输出中获取结果

WITH TOP7 AS
(
SELECT 
SOH.SalesPersonID, SOH.OrderDate,
SUM(SOH.TotalDue) AS Total,
ROW_NUMBER() OVER (PARTITION BY SOH.SalesPersonID ORDER BY SOH.OrderDate DESC) AS RowNum
FROM 
Sales.SalesOrderHeader AS SOH
LEFT JOIN 
Sales.SalesOrderHeader AS SOH2 ON SOH.SalesOrderID = SOH2.SalesOrderID
WHERE 
SOH.SalesPersonID IS NOT NULL 
GROUP BY
SOH.OrderDate, SOH.SalesPersonID
)
SELECT  SalesPersonID, 
Date1 = MAX(CASE WHEN RowNum = 1 THEN OrderDate END),
Total1 = SUM(CASE WHEN RowNum = 1 THEN Total END),
Date2 = MAX(CASE WHEN RowNum = 2 THEN OrderDate END),
Total2 = SUM(CASE WHEN RowNum = 2 THEN Total END),
Date3 = MAX(CASE WHEN RowNum = 3 THEN OrderDate END),
Total3 = SUM(CASE WHEN RowNum = 3 THEN Total END),
Date4 = MAX(CASE WHEN RowNum = 4 THEN OrderDate END),
Total4 = SUM(CASE WHEN RowNum = 4 THEN Total END),
Date5 = MAX(CASE WHEN RowNum = 5 THEN OrderDate END),
Total5 = SUM(CASE WHEN RowNum = 5 THEN Total END),
Date6 = MAX(CASE WHEN RowNum = 6 THEN OrderDate END),
Total6 = SUM(CASE WHEN RowNum = 6 THEN Total END),
Date7 = MAX(CASE WHEN RowNum = 7 THEN OrderDate END),
Total7 = SUM(CASE WHEN RowNum = 7 THEN Total END)
FROM    TOP7
WHERE   RowNum <= 7
GROUP BY SalesPersonID;

DB<>Fiddle 上的示例

但是，以这种格式存储数据可能不是一个好主意，如果您需要经常访问这种格式的数据，最好创建一个视图。我也在这里删除了你的加入，因为这似乎完全没有意义，因为你从来没有引用SOH2：

CREATE VIEW Top7SalesPivot
AS
WITH TOP7 AS
(
SELECT 
SOH.SalesPersonID, 
SOH.OrderDate,
SUM(SOH.TotalDue) AS Total,
ROW_NUMBER() OVER (PARTITION BY SOH.SalesPersonID ORDER BY SOH.OrderDate DESC) AS RowNum
FROM 
Sales.SalesOrderHeader AS SOH
WHERE 
SOH.SalesPersonID IS NOT NULL 
GROUP BY
SOH.OrderDate, SOH.SalesPersonID
)
SELECT  SalesPersonID, 
Date1 = MAX(CASE WHEN RowNum = 1 THEN OrderDate END),
Total1 = SUM(CASE WHEN RowNum = 1 THEN Total END),
Date2 = MAX(CASE WHEN RowNum = 2 THEN OrderDate END),
Total2 = SUM(CASE WHEN RowNum = 2 THEN Total END),
Date3 = MAX(CASE WHEN RowNum = 3 THEN OrderDate END),
Total3 = SUM(CASE WHEN RowNum = 3 THEN Total END),
Date4 = MAX(CASE WHEN RowNum = 4 THEN OrderDate END),
Total4 = SUM(CASE WHEN RowNum = 4 THEN Total END),
Date5 = MAX(CASE WHEN RowNum = 5 THEN OrderDate END),
Total5 = SUM(CASE WHEN RowNum = 5 THEN Total END),
Date6 = MAX(CASE WHEN RowNum = 6 THEN OrderDate END),
Total6 = SUM(CASE WHEN RowNum = 6 THEN Total END),
Date7 = MAX(CASE WHEN RowNum = 7 THEN OrderDate END),
Total7 = SUM(CASE WHEN RowNum = 7 THEN Total END)
FROM    TOP7
WHERE   RowNum <= 7
GROUP BY SalesPersonID;

这样，您无需在每次添加销售时都更新表。

如果这表现不佳，那么您可能会发现以下索引有所帮助：

CREATE NONCLUSTERED INDEX IX_Sales_SalesOrderHeader__SalesPersonID_OrderDate 
ON Sales.SalesOrderHeader (SalesPersonID, OrderDate) 
INCLUDE (TotalDue);

Answer 2

不需要光标。SQL是一种基于集合的语言，其中这样的操作很容易在没有循环的情况下执行。您可以只使用insert ... select语法：

WITH TOP7 AS (
SELECT 
SOH.SalesPersonID, SOH.OrderDate,
SUM(SOH.TotalDue) AS Total,
ROW_NUMBER() OVER (PARTITION BY SOH.SalesPersonID ORDER BY SOH.OrderDate DESC) AS RowNum
FROM 
Sales.SalesOrderHeader AS SOH
LEFT JOIN 
Sales.SalesOrderHeader AS SOH2 ON SOH.SalesOrderID = SOH2.SalesOrderID
WHERE 
SOH.SalesPersonID IS NOT NULL 
GROUP BY
)
INSERT INTO mytable(ID, Fdate, Ftotal, Sdate, Stotal, Tdate, Ttotal, Fdate, Ftotal, Fidate, Fitotal, Sidate, Sitotal, Sedate, Stotal)
SELECT SalesPersonID, OrderDate, Total, RowNum
FROM TOP7
WHERE RowNum <= 7