这里有一张简单的数据表
编号 姓名 成绩
1 张三 80
2 李四 90
3 王二 85
4 麻子 77
5 小明 79
... ... ...
(数据很多)
但要求纵向分页显示，每页就显示3条数据，如下表所示：
编号 1 2 3
姓名 张三 李四 王二
成绩 80 90 85
------------------------------// 这样分开显示
编号 4 5 ...
姓名 麻子 小明 ...
成绩 77 79 ...
------------------------------
继续分页...
用SQL语句如何来处理？
---------------------------------------------------------------
编号是不是不中断！
create proc getreport
@PageNo int
as
declare @temp table (id int IDENTITY(1,1),Name varchar(20),One varchar(30) null,Two Varchar(30) null,Three varchar(30) null)
insert @Temp (name) values('编号')
insert @Temp (name) values('姓名')
insert @Temp (name) values('成绩')
update @Temp
set One=cast(b.编号 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3-2
and a.id=1
update @Temp
set Two=cast(b.编号 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3-1
and a.id=1
update @Temp
set Three=cast(b.编号 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3
and a.id=1
update @Temp
set One=b.姓名
from @Temp a,tablename b
where b.编号=@PageNo*3-2
and a.id=2
update @Temp
set Two=b.姓名
from @Temp a,tablename b
where b.编号=@PageNo*3-1
and a.id=2
update @Temp
set Three=b.姓名
from @Temp a,tablename b
where b.编号=@PageNo*3
and a.id=2
update @Temp
set One=cast(b.成绩 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3-2
and a.id=3
update @Temp
set Two=cast(b.成绩 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3-1
and a.id=3
update @Temp
set Three=cast(b.成绩 as varchar(30))
from @Temp a,tablename b
where b.编号=@PageNo*3
and a.id=3
select Name,One,Two,Three
from @Temp
go
调用：
exec getreport 1
---------------------------------------------------------------
create table #aa (a char(),b char(),c char(),d int ,e int)
declare maxbh int,ii int
select maxbh = max(bh) from table
set ii =1
WHILE ii < =maxbh
begin
insert into #aa (a,b,c,d,e)
select ....,ii from table where bh = ii
insert into #aa (a,b,c,d,e)
select ....,ii from table where bh = ii+1
insert into #aa (a,b,c,d,e)
select ....,ii from table where bh = ii+2
set ii = ii + 3
end
select * from #aa order by e
的确是难为SQL啦，按以上的思路自己改改,多少可以得到一点类似的结果。
---------------------------------------------------------------
CCEO() 说得没错，编号是不是连续的？如果不连续，倒到临时表运行
下面测试一个，供参考：
建表：
create table ta (id int,name varchar(10),num int)
insert ta select 1, '张三', 80
union all select 2, '李四', 90
union all select 3, '王二', 85
union all select 4, '麻子', 77
union all select 5, '小明', 79
union all select 6, 'a', 80
union all select 7, 'b', 81
union all select 8, 'c', 82
语句：
select bh,col1,col2,col3,page from (
select '编号' as bh,
cast(max(case when id%3=1 then id else '' end) as varchar(10))as col1,
cast(max(case when id%3=2 then id else '' end) as varchar(10)) as col2,
cast(max(case when id%3=0 then id else '' end) as varchar(10)) as col3,
(id-1)/3+1 as page,
1 as N
from ta group by (id-1)/3+1
union all
select '姓名' as bh,
max(case when id%3=1 then name else '' end) as col1,
max(case when id%3=2 then name else '' end) as col2,
max(case when id%3=0 then name else '' end) as col3,
(id-1)/3+1 as page,
2 as N
from ta group by (id-1)/3+1
union all
select '成绩' as bh,
cast(max(case when id%3=1 then num else '' end) as varchar(10)) as col1,
cast(max(case when id%3=2 then num else '' end) as varchar(10)) as col2,
cast(max(case when id%3=0 then num else '' end) as varchar(10)) as col3,
(id-1)/3+1 as page,
3 as N
from ta group by (id-1)/3+1
) a
order by page,N
结果：
bh col1 col2 col3 page
---- ---------- ---------- ---------- -----------
编号 1 2 3 1
姓名 张三 李四 王二 1
成绩 80 90 85 1
编号 4 5 6 2
姓名 麻子 小明 a 2
成绩 77 79 80 2
编号 7 8 0 3
姓名 b c 3
成绩 81 82 0 3