to lsxaa(小李铅笔刀) : 我就是这样设计表的,在前台读取、后台控制任意章节和分卷的指定排列顺序可以实现。我觉得如果不是这样设计表,在章节中读取上一页,下一页就不行。下面是我的存储过程中读取上一页,下一页的语句。SELECT @LastChapter = ChapterID FROM Chapter WHERE BookID = @BookID AND SortID<@SortIDSELECT @NextChapter = BookChapter.ChapterID FROM Chapter,Volume WHERE Chapter.BookID = @BookID AND Chapter.SortID>@SortID ORDER BY Volume.SortID,Chapter.SortID DESC
谢谢lsxaa(小李铅笔刀) 的回答,按你的提议来已经实现这个功能,下面是我的存储过程:------------------ DECLARE @SortID INT DECLARE @VolumeID INT --获取排序ID,卷ID SELECT @SortID = SortID,@VolumeID=VolumeID FROM BookChapter WHERE BookID = @BookID AND ChapterID=@ChapterID--获取上一页记录 IF EXISTS (SELECT * FROM BookChapter WHERE VolumeID=@VolumeID AND SortID < @SortID) BEGIN SELECT @LastChapter = MAX(ChapterID) FROM BookChapter WHERE BookID = @BookID AND VolumeID=@VolumeID AND SortID < @SortID END ELSE BEGIN SELECT @LastChapter = MAX(ChapterID) FROM BookChapter WHERE BookID = @BookID AND VolumeID=(SELECT TOP 1 VolumeID FROM BookVolume WHERE BookID = @BookID AND VolumeID<@VolumeID ORDER BY SortID DESC) END--获取下一页记录 IF EXISTS (SELECT * FROM BookChapter WHERE VolumeID=@VolumeID AND SortID > @SortID) BEGIN SELECT @NextChapter = MIN(ChapterID) FROM BookChapter WHERE BookID = @BookID AND VolumeID=@VolumeID AND SortID > @SortID END ELSE BEGIN SELECT @NextChapter = MIN(ChapterID) FROM BookChapter WHERE BookID = @BookID AND VolumeID=(SELECT TOP 1 VolumeID FROM BookVolume WHERE VolumeID>@VolumeID ORDER BY SortID) END ------------------对后台管理用的存储过程的确是减少了很多工作。这样的语句有否可改进之处?请大家多多指教。
我就是这样设计表的,在前台读取、后台控制任意章节和分卷的指定排列顺序可以实现。我觉得如果不是这样设计表,在章节中读取上一页,下一页就不行。下面是我的存储过程中读取上一页,下一页的语句。SELECT @LastChapter = ChapterID FROM Chapter WHERE BookID = @BookID AND SortID<@SortIDSELECT @NextChapter = BookChapter.ChapterID FROM Chapter,Volume WHERE Chapter.BookID = @BookID AND Chapter.SortID>@SortID ORDER BY Volume.SortID,Chapter.SortID DESC
ChapterID
VolumeID
ChapterTitle
SortID --根据所属分卷的Volume.SortID + 1。101,102,103.....Volume表:
VolumeID
VolumeName
BookID
SortID --基数为100叠加,为的是分卷里能容纳100个章节。100,200,300.....--- 正常的前台网页 --- 分卷1 --根据Volume.SortID排序
章节1 章节2 章节3 章节4 --根据Chapter.SortID排序 分卷2
章节5 章节6 章节7 章节8 分卷3
章节9 章节10 章节11 章节12
--- 修改[分卷2]列到顺序第一 ---
分卷2
章节5 章节6 章节7 章节8 分卷1
章节1 章节2 章节3 章节4 分卷3
章节9 章节10 章节11 章节12
(这里不讨论修改顺序后章节名是否有错),在网页中从[章节5]按顺序按[下一页]浏览到[章节8]后就会跳到[分卷3]的章节9,这就是我为什么要建立分组编号(应该这个吧?呵)。
问题来了:把Volume.SortID重新编号后,怎么把Chapter.SortID重新编号?该怎样写UPDATE语句?
ChapterID
VolumeID
ChapterTitle
SortID --根据所属分卷的Volume.SortID + 1。101,102,103.....这里SortId 不要和Volume.SortID 关联起来.... 因为你已经有一个VolumeID字段来写之关联了也就是说SortID 按照自己的顺序排... 1 2 3 4.....
DECLARE @SortID INT
DECLARE @VolumeID INT
--获取排序ID,卷ID
SELECT @SortID = SortID,@VolumeID=VolumeID FROM BookChapter WHERE BookID = @BookID AND ChapterID=@ChapterID--获取上一页记录
IF EXISTS (SELECT * FROM BookChapter WHERE VolumeID=@VolumeID AND SortID < @SortID)
BEGIN
SELECT @LastChapter = MAX(ChapterID) FROM BookChapter WHERE BookID = @BookID AND VolumeID=@VolumeID AND SortID < @SortID
END
ELSE
BEGIN
SELECT @LastChapter = MAX(ChapterID) FROM BookChapter WHERE BookID = @BookID AND VolumeID=(SELECT TOP 1 VolumeID FROM BookVolume WHERE BookID = @BookID AND VolumeID<@VolumeID ORDER BY SortID DESC)
END--获取下一页记录
IF EXISTS (SELECT * FROM BookChapter WHERE VolumeID=@VolumeID AND SortID > @SortID)
BEGIN
SELECT @NextChapter = MIN(ChapterID) FROM BookChapter WHERE BookID = @BookID AND VolumeID=@VolumeID AND SortID > @SortID
END
ELSE
BEGIN
SELECT @NextChapter = MIN(ChapterID) FROM BookChapter WHERE BookID = @BookID AND VolumeID=(SELECT TOP 1 VolumeID FROM BookVolume WHERE VolumeID>@VolumeID ORDER BY SortID)
END
------------------对后台管理用的存储过程的确是减少了很多工作。这样的语句有否可改进之处?请大家多多指教。