34,588
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE A(ID int, PAGES int)
INSERT INTO A VALUES (1,100)
CREATE TABLE B(ID int, SN int, PAGENO int)
INSERT INTO B VALUES(1,1,1)
INSERT INTO B VALUES(1,2,3)
INSERT INTO B VALUES(1,3,5)
INSERT INTO B VALUES(1,4,7)
INSERT INTO B VALUES(1,5,9)
SELECT B1.PAGENO 页号,
B2.PAGENO-B1.PAGENO 页数
FROM B B1
JOIN (SELECT * FROM B
UNION ALL
SELECT ID,
(SELECT MAX(SN)+1 FROM B WHERE B.ID = A.ID),
PAGES+1
FROM A
) B2
ON B2.ID = B1.ID
AND B2.SN = B1.SN+1
WHERE B1.ID = 1
页号 页数
----------- -----------
1 2
3 2
5 2
7 2
9 92
SELECT B1.PAGENO 页号,
B2.PAGENO-B1.PAGENO 页数,
B2.总页数
FROM B B1
JOIN (SELECT *,NULL 总页数 FROM B
UNION ALL
SELECT ID,
(SELECT MAX(SN)+1 FROM B WHERE B.ID = A.ID),
PAGES+1,
PAGES 总页数
FROM A
) B2
ON B2.ID = B1.ID
AND B2.SN = B1.SN+1
WHERE B1.ID = 1