求一SQL语句，关于分段查询
求一SQL语句或者存储过程，关于分段查询，表结构如下
id datetime
1 2007-03-03
2 2007-03-05
3 2007-03-08
5 2007-03-11
7 2007-03-16
9 2007-03-20
11 2007-04-26
要求按N来分组
比如，N=2
输出结果
1 2007-03-03
2 2007-03-05
---------------
3 2007-03-08
5 2007-03-11
--------------
7 2007-03-16
9 2007-03-20
--------------
11 2007-04-26
N=3
输出结果
1 2007-03-03
2 2007-03-05
3 2007-03-08
---------------
5 2007-03-11
7 2007-03-16
9 2007-03-20
---------------
11 2007-04-26
[解决办法]
偶感覺怎麼像分頁?
還是LZ想顯示成那個樣子...
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id int,dt varchar(10))
insert into tb(id,dt) values(1, '2007-03-03 ')
insert into tb(id,dt) values(2, '2007-03-05 ')
insert into tb(id,dt) values(3, '2007-03-08 ')
insert into tb(id,dt) values(5, '2007-03-11 ')
insert into tb(id,dt) values(7, '2007-03-16 ')
insert into tb(id,dt) values(9, '2007-03-20 ')
insert into tb(id,dt) values(11, '2007-04-26 ')
go
declare @N as int
declare @I as int
declare @cnt as int
declare @J as int
select px=identity(int,1,1) , * into test from tb
select @cnt = count(*) from test
set @N = 2
set @J = @cnt / @N
set @I = 1
declare @id as varchar(10)
declare @dt as varchar(10)
while @I <= @J
begin
declare @K as int
set @K = 1
while @K <= @N
begin
select @id = cast(id as varchar) from test where px = @K + @N*(@I - 1)
select @dt = dt from test where px = @K + @N*(@I - 1)
print @id + ' ' + @dt
set @K = @K + 1
end
print '----------- '
set @I = @I + 1
end
set @K = @cnt % @N
set @I = 1
while @I <= @K
begin
select @id = cast(id as varchar) from test where px = @I + @N*@J
select @dt = dt from test where px = @I + @N*@J
print @id + ' ' + @dt
set @I = @I + 1
end
drop table tb,test
/*
1 2007-03-03
2 2007-03-05
-----------
3 2007-03-08
5 2007-03-11
-----------
7 2007-03-16
9 2007-03-20
-----------
11 2007-04-26
*/
[解决办法]
drop table tbtest
create table tbtest(id int,[datetime] datetime)
insert into tbtest
select 1, '2007-03-03 '
union all select 2, '2007-03-05 '
union all select 3, '2007-03-08 '
union all select 5, '2007-03-11 '
union all select 7, '2007-03-16 '
union all select 9, '2007-03-20 '
union all select 11, '2007-04-26 '
alter table tbtest add id1 int
go
update tbtest
set id1=(select count(*) from tbtest t where t.id <=tbtest.id)
go
create table #t(strs varchar(20))
go
declare @id int,@datetime datetime,@id1 int,@n int
set @n=2
declare cur_tmp cursor for
select id,[datetime],id1 from tbtest
open cur_tmp
fetch next from cur_tmp into @id,@datetime,@id1
while @@fetch_status=0
begin
if @id1%@n=0
begin
insert into #t(strs)
select rtrim(@id)+replicate( ' ',10-len(@id))+convert(char(10),@datetime,120)
union all
select replicate( '- ',20)
end
else
begin
insert into #t(strs)
select rtrim(@id)+replicate( ' ',10-len(@id))+convert(char(10),@datetime,120)
end
fetch next from cur_tmp into @id,@datetime,@id1
end
close cur_tmp
deallocate cur_tmp
go
alter table tbtest drop column id1
go
select strs from #t
go
drop table #t
[解决办法]
没必要弄这么复杂吧?楼主你到底是要分段还是分页啊!
[解决办法]
iden id datetime
----------- ----------- -----------------------
1 1 2007-03-03 00:00:00.000
2 2 2007-03-05 00:00:00.000
(2 行受影响)
iden id datetime
----------- ----------- -----------------------
3 3 2007-03-08 00:00:00.000
4 5 2007-03-11 00:00:00.000
(2 行受影响)
iden id datetime
----------- ----------- -----------------------
5 7 2007-03-16 00:00:00.000
6 9 2007-03-20 00:00:00.000
(2 行受影响)
iden id datetime
----------- ----------- -----------------------
7 11 2007-04-26 00:00:00.000
(1 行受影响)
