时间戳

首页 > 解决方案 > 我可以在 T-SQL 中使用 while 或 cursor 插入新表吗

sql-server - 我可以在 T-SQL 中使用 while 或 cursor 插入新表吗

问题描述

我写了一个查询来获取 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)

^ 我知道这是错误的，但这是我正在尝试做的，因为我需要在未来做很多事情，所以我需要知道一个好的方法来实现它

标签： sql-serverloopstsqlstored-proceduressql-insert

解决方案

不需要光标。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

推荐阅读