你看下这个执行结果是你想要的么?我是在PL/SQL DEVELOPER写的
WITH V AS(
SELECT 1 ITEM_NO,'000' OWNER_ID,81 AMOUNT_OF_PAGED FROM DUAL UNION ALL
SELECT 2,'000',5 FROM DUAL UNION ALL
SELECT 3,'000',5 FROM DUAL UNION ALL
SELECT 4,'000',8 FROM DUAL UNION ALL
SELECT 5,'000',11 FROM DUAL UNION ALL
SELECT 6,'000',18 FROM DUAL UNION ALL
SELECT 7,'000',1 FROM DUAL UNION ALL
SELECT 8,'000',24 FROM DUAL UNION ALL
SELECT 9,'000',11 FROM DUAL UNION ALL
SELECT 10,'000',14 FROM DUAL
)
SELECT V.ITEM_NO,V.OWNER_ID,V.AMOUNT_OF_PAGED,
NVL(SUM(AMOUNT_OF_PAGED) --SUM()分析函数
OVER(PARTITION BY OWNER_ID ORDER BY ITEM_NO
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0)+1 PAGE_NO
--要不想第一行改变值,可以将终点设置为1 PRECEDING,即前一行,当扫描第一行时,
--也就是从第一行起点到它的前一行,前一行为空,第一行就不会改变值
FROM V;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] INT,PAGE_NO int)
Insert #T
select 1,81,0 union all
select 2,5,0 union all
select 3,5,0 union all
select 4,8,0 union all
select 5,11,0 union all
select 6,18,0 union all
select 7,1,0 union all
select 8,24,0 union all
select 9,11,0 union all
select 10,14,0
Go
--测试数据结束
;WITH cte AS (
Select *,1 AS tempPAGE_NO from #T WHERE item_no=1
UNION ALL
SELECT #T.*,cte.PAGE_NO+cte.AMOUNT_OF_PAGED FROM #T JOIN cte ON cte.item_no+1 = #T.item_no
)
UPDATE #T SET PAGE_NO=cte.tempPAGE_NO FROM cte WHERE #T.item_no=cte.item_no
SELECT * FROM #T
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
--测试数据结束
;WITH cte AS (
Select *,1 AS PAGE_NO from #T WHERE item_no=1
UNION ALL
SELECT #T.*,cte.PAGE_NO+cte.AMOUNT_OF_PAGED FROM #T JOIN cte ON cte.item_no+1 = #T.item_no
)
SELECT * FROM cte
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
--测试数据结束
;WITH cte AS (
Select *,1 AS PAGE_NO from #T WHERE item_no=1
UNION ALL
SELECT #T.*,cte.PAGE_NO+cte.AMOUNT_OF_PAGED FROM #T JOIN cte ON cte.item_no+1 = #T.item_no
)
SELECT * FROM cte
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
--测试数据结束
;WITH cte AS (
Select *,1 AS PAGE_NO from #T WHERE item_no=1
UNION ALL
SELECT #T.*,cte.PAGE_NO+cte.AMOUNT_OF_PAGED FROM #T JOIN cte ON cte.item_no+1 = #T.item_no
)
SELECT * FROM cte
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
--测试数据结束
;WITH cte AS (
Select *,1 AS PAGE_NO from #T WHERE item_no=1
UNION ALL
SELECT #T.*,cte.PAGE_NO+cte.AMOUNT_OF_PAGED FROM #T JOIN cte ON cte.item_no+1 = #T.item_no
)
SELECT * FROM cte
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
SELECT A.*,
CASE WHEN ITEM_NO=(SELECT MAX(ITEM_NO) FROM #T) THEN CAST(B.PAGE_NO+1 AS VARCHAR)+'-'+CAST(B.PAGE_NO+AMOUNT_OF_PAGED AS VARCHAR)
WHEN ISNULL(B.PAGE_NO,0)=0 THEN '1'
ELSE CAST(B.PAGE_NO+1 AS VARCHAR) END AS PAGE_NO
FROM #T A
OUTER APPLY (SELECT SUM(AMOUNT_OF_PAGED) AS PAGE_NO FROM #T WHERE item_no<A.item_no) AS B
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
--测试数据结束
;WITH cte AS (
Select *,1 AS PAGE_NO from #T WHERE item_no=1
UNION ALL
SELECT #T.*,cte.PAGE_NO+cte.AMOUNT_OF_PAGED FROM #T JOIN cte ON cte.item_no+1 = #T.item_no
)
SELECT * FROM cte
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
--测试数据结束
;WITH cte AS (
Select *,1 AS PAGE_NO from #T WHERE item_no=1
UNION ALL
SELECT #T.*,cte.PAGE_NO+cte.AMOUNT_OF_PAGED FROM #T JOIN cte ON cte.item_no+1 = #T.item_no
)
SELECT * FROM cte
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
SELECT A.*,
CASE WHEN ITEM_NO=(SELECT MAX(ITEM_NO) FROM #T) THEN CAST(B.PAGE_NO+1 AS VARCHAR)+'-'+CAST(B.PAGE_NO+AMOUNT_OF_PAGED AS VARCHAR)
WHEN ISNULL(B.PAGE_NO,0)=0 THEN '1'
ELSE CAST(B.PAGE_NO+1 AS VARCHAR) END AS PAGE_NO
FROM #T A
OUTER APPLY (SELECT SUM(AMOUNT_OF_PAGED) AS PAGE_NO FROM #T WHERE item_no<A.item_no) AS B
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([item_no] int,[AMOUNT_OF_PAGED] int)
Insert #T
select 1,81 union all
select 2,5 union all
select 3,5 union all
select 4,8 union all
select 5,11 union all
select 6,18 union all
select 7,1 union all
select 8,24 union all
select 9,11 union all
select 10,14
Go
--测试数据结束
;WITH cte AS (
Select *,1 AS PAGE_NO from #T WHERE item_no=1
UNION ALL
SELECT #T.*,cte.PAGE_NO+cte.AMOUNT_OF_PAGED FROM #T JOIN cte ON cte.item_no+1 = #T.item_no
)
SELECT * FROM cte