代码之家 › 专栏 › 技术社区 › JohnFx

需要一些tsql向导:基于running total的sql更新

sql-server-2008 tsql

JohnFx · 技术社区 · 15 年前

我已经有了一个实现,它使用了一个非常难以遵循/维护的超毛茸茸的递归cte。我希望So上的一个大脑能够想出一些更直接的TSQL方法代码来完成以下任务:

表格文档

DocID    SortOrder    PageCount    StartPgNum   EndPgNum
5        1            2               {1}          {2}
8        2            7               {3}          {9}
22       3            3               {10}         {12}

对于上面给出的表,我需要一个查询来填充startpgnum和endpgnum(示例值包含在{}中,以便更清楚地说明我需要什么。

假设:
*docid、sortorder和pagecount是预先填充的。
*startpgnum和endgnum需要由tsql代码填充。
*sortorder总是从1开始,并且是连续的,没有间隙。
*文档应该按照sortorder的顺序得到一个连续的页码方案

6 回复 | 直到 15 年前

JohnFx 15 年前

更新为更好:)

DECLARE @temp TABLE (DocID INT, SortOrder INT, PageCount INT)

INSERT INTO @temp VALUES (5, 1, 2)
INSERT INTO @temp VALUES (8, 2, 7)
INSERT INTO @temp VALUES (22, 3, 3)

SELECT
    *,
    StartPgNum + PageCount-1 AS EndPgNum
FROM
(SELECT
    DocID,
    SortOrder,
    PageCount,
    ISNULL((SELECT SUM(PageCount)+1 FROM @temp WHERE SortOrder < parent.SortOrder), 1) AS StartPgNum
FROM
    @temp parent) _temp

JohnFx 15 年前

我对其他答案中提供的所有解决方案做了一些测试,我原来的“hairy recursive cte”选项,为了完整起见,我使用了一种简单的基于光标的方法。令我大吃一惊的是,游标选项在我的所有测试(1K行、10K行、50K行、500K行)中都以明显的优势发挥了最好的性能。

以下是10K记录每次进近的平均次数:
毛茸茸的递归CTE: 3分55秒
交叉应用(Ben Dempsey): 21-25秒
子选举(Tim Khouri): 19-21秒
光标: 1-2秒

这是我基于光标的解决方案:

Declare @temp TABLE(
 DocID INT PRIMARY KEY NOT NULL, 
 SortOrder INT NOT NULL, 
 PageCount INT NOT NULL,
 BegPg int,
 EndPg int
)

Insert into @temp (DocID,SortOrder,PageCount) 
SELECT top 50000 docid, ROW_NUMBER() OVER (ORDER BY DOCID),Pages FROM tblDocuments

DECLARE @PC int
SET @PC=1
DECLARE @FetchPageCount int
DECLARE @FetchDocID int

DECLARE myCursor CURSOR FOR 
SELECT DocID, PageCount FROM @temp ORDER BY SortOrder

OPEN myCursor
FETCH NEXT FROM myCursor INTO @FetchDocID,@FetchPageCount

WHILE @@FETCH_STATUS = 0
BEGIN

  UPDATE @temp SET BegPg=@PC, EndPg=@PC+ @FetchPageCount-1   
  WHERE (Docid=@fetchDocid)

     SET @PC = @PC + @FetchPageCount

    FETCH NEXT FROM myCursor INTO @FetchDocID,@FetchPageCount
END 
CLOSE myCursor
DEALLOCATE myCursor

SELECT * FROM @temp

谁会猜到呢?也许诅咒并不总是邪恶的。

一句警告:为了避免您试图将更新替换为“where current of mycursor”语法,它的执行速度比使用当前版本的where子句慢得多,尽管仍然比大多数其他方法快。

Matt Whitfield 15 年前

最快的方法是 Quirky Update . 这取决于你是否陷入“微软没有明确说它有效,所以我会避免”IT阵营或不…

否则,您将处于毛茸茸的递归cte(正如您已经发现的那样)或三角形连接(这可能成为大型数据集上的一个噩梦)领域。

AaronLS 15 年前

也许这三种解决方案中的一种会有所帮助,因为这是一种“总运行”问题: http://www.sqlteam.com/article/calculating-running-totals

Ben Dempsey 15 年前

使用交叉应用的SQL 2008(运行总数)

/*
DocID    SortOrder    PageCount    StartPgNum   EndPgNum
5        1            2               {1}          {2}
8        2            7               {3}          {9}
22       3            3               {10}        {12}
*/

Declare @MyTable TABLE(
DocID int,
SortOrder int,
PageCount int
)

Insert into @MyTable(DocID,SortOrder,PageCount)
values (5,1,2), (8,2,7), (22,3,3)

select 
    T1.docID, 
    T1.Sortorder, 
    T1.Pagecount, 
    (T.RunningTotal - T1.Pagecount) + 1 StartPgNum , 
    T.RunningTotal EndPgNum

FROM    @MyTable T1
        CROSS APPLY ( Select SUM(PageCount) RunningTotal FROM @MyTable where SortOrder <= T1.SortOrder) T
order by T1.sortorder

awright18 15 年前

我选择通过创建函数来解决这两个问题,一个是获取第一页,另一个是获取最后一页。下面是可以工作的函数和查询。

CREATE FUNCTION dbo.GetFirstPage(@SortOrder int) 
RETURNS int
as
BEGIN
DECLARE @FirstPage int
SET @FirstPage = 1
IF(@SortOrder > 1)
BEGIN
SELECT @FirstPage = SUM(PageCount) + 1
FROM Documents
WHERE SortOrder < @SortOrder
END
RETURN @FirstPage
END

CREATE FUNCTION dbo.GetLastPage(@FirstPage int, @PageCount int)
RETURNS int 
AS
BEGIN
RETURN (@FirstPage + @PageCount -1)
END

最后是询问。

SELECT * ,  
        dbo.GetFirstPage(SortOrder) AS FirstPage,
        dbo.GetLastPage(dbo.GetFirstPage(SortOrder),Pagecount) AS LastPage 
FROM Documents