日期:2014-05-18  浏览次数:20672 次

Sql Server如何隔行查询
sql语句如下:
Select ChapterID,ChapterName From Chapter Where BookID=111

执行后的结果:
ChapterID ChapterName
2664091 拽丫头与校草同居1
2664092 拽丫头与校草同居2
2664093 拽丫头与校草同居3
2664094 拽丫头与校草同居4
2664095 拽丫头与校草同居5
2664096 拽丫头与校草同居6
2664097 拽丫头与校草同居7
2664098 拽丫头与校草同居8
2664099 拽丫头与校草同居9
2664100 拽丫头与校草同居10
2664101 拽丫头与校草同居11
2664102 拽丫头与校草同居12
2664103 拽丫头与校草同居13
2664104 拽丫头与校草同居14
2664105 拽丫头与校草同居15
2664106 拽丫头与校草同居16
.....
2666634 拽丫头与校草同居3888
2666635 拽丫头与校草同居3889
2666636 拽丫头与校草同居3890

问题:如何才能做到隔2行或者隔3行查一条结果?(注:第一章即第一条记录必须出现在结果中)结果示例如下:
2664091 拽丫头与校草同居1
2664094 拽丫头与校草同居4
2664097 拽丫头与校草同居7
...
2666636 拽丫头与校草同居3890


请高手把SQL的写法贴出来,或者给一个详细的解决方案,谢谢。(因为是高访问量,请注意查询效率的问题)
另外,对于高查询量的小说数据库,请给一个比较好的数据结构设计方案?
现在的数据库设计方案是:
小说表Book(BookID,BookName)
章节表Chapter(ChapterID,BookID,ChapterName)
这种数据结构是否有可以优化的地方?

------解决方案--------------------
SQL code
Select (Select row_number() over(order by ChapterID) From Chapter ) id ,ChapterID From Chapter
Where BookID=111  and id%3=0

------解决方案--------------------
SQL code

use tempdb;
/*
create table A
(
    ChapterID nvarchar(10) not null,
    ChapterName nvarchar(20) not null
);
insert into A values
('2664091','拽丫头与校草同居1'),
('2664092','拽丫头与校草同居2'),
('2664093','拽丫头与校草同居3'),
('2664094','拽丫头与校草同居4'),
('2664095','拽丫头与校草同居5'),
('2664096','拽丫头与校草同居6'),
('2664097','拽丫头与校草同居7');
*/
select B.ChapterID,B.ChapterName
from
(
    select *,row_number() over(order by ChapterID) as [orderno] from A
) as B
where B.[orderno]%3=1;

------解决方案--------------------
SQL code
Select (Select row_number() over(order by ChapterID) From Chapter ) id ,ChapterID From Chapter
Where BookID=111  and id%3=0

------解决方案--------------------
SQL code

-- 建索引
create index idx_Chapter_BookID on Chapter(BookID)

-- 查询,且取第一行和最后一行.
with t as
(select ChapterID,
 row_number() over(order by ChapterID) id 
 from Chapter where BookID=111
)
select id,ChapterID from t
where floor(id)%3=0 or id=1
or id=(select max(id) from t)

------解决方案--------------------
SQL code

declare @T table (ChapterID int,ChapterName varchar(18))
insert into @T
select 2664091,'拽丫头与校草同居1' union all
select 2664092,'拽丫头与校草同居2' union all
select 2664093,'拽丫头与校草同居3' union all
select 2664094,'拽丫头与校草同居4' union all
select 2664095,'拽丫头与校草同居5' union all
select 2664096,'拽丫头与校草同居6' union all
select 2664097,'拽丫头与校草同居7' union all
select 2664098,'拽丫头与校草同居8' union all
select 2664099,'拽丫头与校草同居9' union all
select 2664100,'拽丫头与校草同居10' union all
select 2664101,'拽丫头与校草同居11' union all
select 2664102,'拽丫头与校草同居12' union all
select 2664103,'拽丫头与校草同居13' union all
select 2664104,'拽丫头与校草同居14' union all
select 2664105,'拽丫头与校草同居15' union all
select 2664106,'拽丫头与校草同居16'

;with maco as 
(
select row_number() over (order by (select 1)) as num,* from @T
)
select ChapterID,ChapterName from  maco where num%3=1
/*
ChapterID   ChapterName
----------- ------------------
2664091     拽丫头与校草同居1
2664094     拽丫头与校草同居4
2664097     拽丫头与校草同居7
2664100     拽丫头与校草同居10
2664103     拽丫头与校草同居13
2664106     拽丫头与校草同居16
*/

------解决方案--------------------
or id=1就是取第一条呀,测试如下,
SQL code

create table Chapter(BookID int, ChapterID int,ChapterName varchar(18))

insert into Chapter 
select 111, 2664091,'拽丫头与校草同居1' union all
select 111, 2664092,'拽丫头与校草同居2' union all
select 111, 2664093,'拽丫头与校草同居3' union all
select 111, 2664094,'拽丫头与校草同居4' union all
select 111, 2664095,'拽丫头与校草同居5' union all
select 111, 2664096,'拽丫头与校草同居6' union all
select 111, 2664097,'拽丫头与校草同居7' union all
select 111, 2664098,'拽丫头与校草同居8