编号是不是不中断!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=1update @Temp set Two=cast(b.编号 as varchar(20)) from @Temp a,tablename b where b.编号=@PageNo*3-1 and a.id=1update @Temp set Three=cast(b.编号 as varchar(20)) from @Temp a,tablename b where b.编号=@PageNo*3 and a.id=1update @Temp set One=b.姓名 from @Temp a,tablename b where b.编号=@PageNo*3-2 and a.id=2update @Temp set Two=b.姓名 from @Temp a,tablename b where b.编号=@PageNo*3-1 and a.id=2update @Temp set Three=b.姓名 from @Temp a,tablename b where b.编号=@PageNo*3 and a.id=2update @Temp set One=cast(b.成绩 as varchar(20)) from @Temp a,tablename b where b.编号=@PageNo*3-2 and a.id=3update @Temp set Two=cast(b.成绩 as varchar(20)) from @Temp a,tablename b where b.编号=@PageNo*3-1 and a.id=3update @Temp set Three=cast(b.成绩 as varchar(30)) from @Temp a,tablename b where b.编号=@PageNo*3 and a.id=3select Name,One,Two,Three from @Tempgo 调用: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
to j9988(j9988) 表中的编号不是连续的,但我已得到提示,只要编号是连续就可以用你的方法实现了。由于我不想使用临时表,所以我用不等连接给原始数据加上序号以保证序号是连续的。多谢各位!!
@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=1update @Temp
set Two=cast(b.编号 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3-1
and a.id=1update @Temp
set Three=cast(b.编号 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3
and a.id=1update @Temp
set One=b.姓名
from @Temp a,tablename b
where b.编号=@PageNo*3-2
and a.id=2update @Temp
set Two=b.姓名
from @Temp a,tablename b
where b.编号=@PageNo*3-1
and a.id=2update @Temp
set Three=b.姓名
from @Temp a,tablename b
where b.编号=@PageNo*3
and a.id=2update @Temp
set One=cast(b.成绩 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3-2
and a.id=3update @Temp
set Two=cast(b.成绩 as varchar(20))
from @Temp a,tablename b
where b.编号=@PageNo*3-1
and a.id=3update @Temp
set Three=cast(b.成绩 as varchar(30))
from @Temp a,tablename b
where b.编号=@PageNo*3
and a.id=3select Name,One,Two,Three
from @Tempgo
调用:exec getreport 1
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啦,按以上的思路自己改改,多少可以得到一点类似的结果。
下面测试一个,供参考:
建表:
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
表中的编号不是连续的,但我已得到提示,只要编号是连续就可以用你的方法实现了。由于我不想使用临时表,所以我用不等连接给原始数据加上序号以保证序号是连续的。多谢各位!!