У меня есть реализация для этого, которое использует супер волосатый рекурсивный CTE, за которым действительно трудно следовать/поддерживать. Я надеялся, что один из мозгов на ТАК мог придумать еще некоторый простой код подхода 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
Обновлено, чтобы стать лучше :)
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
Самый быстрый способ сделать это - Quirky Update. Это зависит от того, относитесь ли вы к лагерю "Microsoft не говорит прямо, что это работает, поэтому я буду избегать" или нет...
Иначе вы окажетесь на территории волосатого рекурсивного CTE (как вы уже обнаружили) или треугольного join (который может стать кошмаром на большом наборе данных).
SQL 2008 using cross apply (running total)
/*
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
Возможно, одно из этих трех решений может помочь, так как это своего рода проблема с "нарастающим итогом": http://www.sqlteam.com/article/calculating-running-totals
Я провел некоторое тестирование всех решений, представленных здесь в других ответах, моего оригинального варианта "Hairy Recursive CTE" и для полноты картины простого подхода на основе курсора. К моему большому удивлению, вариант с курсором оказался лучшим во всех моих тестах (1K Rows, 10KRows, 50K Rows, 500K Rows)
Вот среднее время для каждого подхода для 10K записей:
Hairy Recursive CTE: 3 минуты 55 секунд
CROSS APPLY (Ben Dempsey): 21-25 секунд
SUBSELECTS (Tim Khouri): 19-21 секунда
CURSOR: 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, хотя все равно быстрее, чем большинство других подходов.
Я решил решить эти две проблемы, создав функции, одну для получения первой страницы, вторую для получения последней страницы. Вот функции и запрос, который будет работать.
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