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

求 SQL 语句
SQL code

if object_id('tblDemoData') is not null
    drop table tblDemoData
go

create table tblDemoData
(
    id int identity(1, 1) primary key,
    number int,
    data varchar(100)
)
go
declare @number int
set @number = 1
while @number < 500
    begin

        insert into tblDemoData
            (number, data) 
            values 
            (@number, 'XXXXXXXXXXXX');
        set @number = @number + 1;

    end
go

/*
根据number进行条件筛选,
有条件如下:
1. 起始10条记录
2. 最后5条记录
3. 中间数据每间隔N条输出N,N+1,N+2

如下(N=15):
    number        data
-------------    ------------------
    1            XXXXXXXXXXXX
    2            XXXXXXXXXXXX
    3            XXXXXXXXXXXX
    ...          ...
    8            XXXXXXXXXXXX
    9            XXXXXXXXXXXX
    10           XXXXXXXXXXXX
    15           XXXXXXXXXXXX
    16           XXXXXXXXXXXX
    17           XXXXXXXXXXXX
    30           XXXXXXXXXXXX
    31           XXXXXXXXXXXX
    32           XXXXXXXXXXXX
    ...
    495          XXXXXXXXXXXX
    496          XXXXXXXXXXXX
    497          XXXXXXXXXXXX
    498          XXXXXXXXXXXX
    499          XXXXXXXXXXXX
*/



------解决方案--------------------
try
SQL code
select * from (select top 10 * from tblDemoData order by id) a
union 
select * from tblDemoData where id%15=0 or (id-1)%15=0 or (id-2)%15=0
union 
select * from (select top 5 * from tblDemoData order by id desc) c

------解决方案--------------------
我没有看懂..是不是想做嵌套循环啊! 
刚开始10条 加着加着变少 最后有5条
------解决方案--------------------
SQL code
create table tblDemoData
(
    id int identity(1, 1) primary key,
    number int,
    data varchar(100)
)
go
declare @number int
set @number = 1
while @number < 500
    begin

        insert into tblDemoData
            (number, data) 
            values 
            (@number, 'XXXXXXXXXXXX');
        set @number = @number + 1;

    end
go

---------------
declare @num as int
set @num = 15

select * from tblDemoData 
where id <=10 or id > (select count(1) from tbldemodata) - 5 
or id between @num and @num + 2

drop table tblDemoData

/*
id          number      data                                                                                                 
----------- ----------- ---------------------------------------------------------------- 
1           1           XXXXXXXXXXXX
2           2           XXXXXXXXXXXX
3           3           XXXXXXXXXXXX
4           4           XXXXXXXXXXXX
5           5           XXXXXXXXXXXX
6           6           XXXXXXXXXXXX
7           7           XXXXXXXXXXXX
8           8           XXXXXXXXXXXX
9           9           XXXXXXXXXXXX
10          10          XXXXXXXXXXXX
15          15          XXXXXXXXXXXX
16          16          XXXXXXXXXXXX
17          17          XXXXXXXXXXXX
495         495         XXXXXXXXXXXX
496         496         XXXXXXXXXXXX
497         497         XXXXXXXXXXXX
498         498         XXXXXXXXXXXX
499         499         XXXXXXXXXXXX

(所影响的行数为 18 行)

*/

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

--每隔5个取一次(适用于SQL SERVER 2005或是2008)
declare @i int set @i=5
;with maco as
(
select * from tblDemoData where id<11 or
id>(select count(1) from tblDemoData) -5 or (id-11)%(@i+1)=0 
)
select distinct b.* from maco a 
left join tblDemoData b on a.id=b.id+1 or a.id=b.id-1 or a.id=b.id