--测试数据
create table test (TEST_NO varchar(10),TEST_NAME varchar(10))
insert into test select 'A0001','张三'
insert into test select 'A0003','李4'
insert into test select 'A0004','李五'
insert into test select 'B0002','王五'
insert into test select 'B0003','将李'
insert into test select 'B0004','五二'
insert into test select 'C0001','王二'
insert into test select 'C0003','邓三'
insert into test select 'C0004','刘4'
go--查询处理的存储过程
CREATE PROC p_PageView
@tbname sysname, --查询的表名
@KeyField sysname, --关键字段名
@SortField sysname,--排序字段名
@Columns int=10, --每页最多显示的列数
@Rows int=10 --每页最多显示的行数
as
set nocount on
declare @s nvarchar(4000),@gid1 varchar(20),@gid2 varchar(20),@pageno varchar(20)
select @s='',
@pageno='1+gid/'+rtrim(@Columns*@rows),
@gid1='(gid%'+rtrim(@Columns*@rows)+')/'+rtrim(@rows),
@gid2='(gid%'+rtrim(@Columns*@rows)+')%'+rtrim(@rows)
while @Columns>0
select @Columns=@Columns-1,
@s='
,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NO end),'''')'
+'
,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NAME end),'''')'
+@s
exec('select PageNo='+@pageno+@s+' from(
select TEST_NO,TEST_NAME,
gid=(select count(*) from test where TEST_NO<a.TEST_NO)
from test a)aa
group by '+@pageno+','+@gid2+'
order by '+@pageno+','+@gid2)
go--调用
exec p_PageView 'test','TEST_NO','TEST_NAME',3,2
go--删除测试
drop table test
drop proc p_PageView/*--结果
---------- ---------- ---------- ---------- ---------- ----------
A0001 张三 B0002 王五 C0001 王二
A0003 李4 B0003 将李 C0003 邓三
A0004 李五 B0004 五二 C0004 刘4
---------- ---------- ---------- ---------- ---------- ----------
A0001 张三 B0003 将李 C0004 刘4
A0003 李4 B0004 五二
A0004 李五 C0001 王二
B0002 王五 C0003 邓三
--*/
create table test (TEST_NO varchar(10),TEST_NAME varchar(10))
insert into test select 'A0001','张三'
insert into test select 'A0003','李4'
insert into test select 'A0004','李五'
insert into test select 'B0002','王五'
insert into test select 'B0003','将李'
insert into test select 'B0004','五二'
insert into test select 'C0001','王二'
insert into test select 'C0003','邓三'
insert into test select 'C0004','刘4'
go--查询处理的存储过程
CREATE PROC p_PageView
@tbname sysname, --查询的表名
@KeyField sysname, --关键字段名
@SortField sysname,--排序字段名
@Columns int=10, --每页最多显示的列数
@Rows int=10 --每页最多显示的行数
as
set nocount on
declare @s nvarchar(4000),@gid1 varchar(20),@gid2 varchar(20),@pageno varchar(20)
select @s='',
@pageno='1+gid/'+rtrim(@Columns*@rows),
@gid1='(gid%'+rtrim(@Columns*@rows)+')/'+rtrim(@rows),
@gid2='(gid%'+rtrim(@Columns*@rows)+')%'+rtrim(@rows)
while @Columns>0
select @Columns=@Columns-1,
@s='
,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NO end),'''')'
+'
,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NAME end),'''')'
+@s
exec('select PageNo='+@pageno+@s+' from(
select TEST_NO,TEST_NAME,
gid=(select count(*) from test where TEST_NO<a.TEST_NO)
from test a)aa
group by '+@pageno+','+@gid2+'
order by '+@pageno+','+@gid2)
go--调用
exec p_PageView 'test','TEST_NO','TEST_NAME',3,2
go--删除测试
drop table test
drop proc p_PageView/*--结果
---------- ---------- ---------- ---------- ---------- ----------
A0001 张三 B0002 王五 C0001 王二
A0003 李4 B0003 将李 C0003 邓三
A0004 李五 B0004 五二 C0004 刘4
---------- ---------- ---------- ---------- ---------- ----------
A0001 张三 B0003 将李 C0004 刘4
A0003 李4 B0004 五二
A0004 李五 C0001 王二
B0002 王五 C0003 邓三
--*/
insert into tb values(1 ,'10001')
insert into tb values(2 ,'10002')
insert into tb values(3 ,'10004')
insert into tb values(4 ,'10005')
insert into tb values(5 ,'10006')
insert into tb values(6 ,'10007')
insert into tb values(7 ,'10008')
insert into tb values(8 ,'10009')
insert into tb values(9 ,'10010')
insert into tb values(10,'10011')
insert into tb values(11,'10012')
go
--静态SQL
select no2 ,
max(case no1 when 0 then no else 0 end) no1,
max(case no1 when 0 then card_id else 0 end) card_id1,
max(case no1 when 1 then no else 0 end) no2,
max(case no1 when 1 then card_id else 0 end) card_id2,
max(case no1 when 2 then no else 0 end) no3,
max(case no1 when 2 then card_id else '' end) card_id3
from
(
select (no-1)/5 no1 , no%5 no2 , no , card_id from tb
) t
group by no2
order by case when no2 = 0 then 2 else 1 end-- drop table tb
用select case...end from 表
--假设字段a的数字是连续的
select a,b,a1=7+a,b1=(select b from A as aa where a=A.a+7)
from A where a<=7
create table #T(ID int,Name varchar(10))
insert into #T values(1 ,'10001')
insert into #T values(2 ,'10002')
insert into #T values(3 ,'10004')
insert into #T values(4 ,'10005')
insert into #T values(5 ,'10006')
insert into #T values(6 ,'10007')
insert into #T values(7 ,'10008')
insert into #T values(8 ,'10009')
insert into #T values(9 ,'10010')
insert into #T values(10,'10011')
insert into #T values(11,'10012') go
select
[ID0]=max(case when (ID-1)/7=0 then ID end),
[Name0]=max(case when (ID-1)/7=0 then Name end),
[ID1]=max(case when (ID-1)/7=1 then ID else '' end),
[Name1]=max(case when (ID-1)/7=1 then Name else '' end)
from
#T
group by (ID-1)%7ID0 Name0 ID1 Name1
----------- ---------- ----------- ----------
1 10001 8 10009
2 10002 9 10010
3 10004 10 10011
4 10005 11 10012
5 10006 0
6 10007 0
7 10008 0 (所影响的行数为 7 行)
[ID0]=max(case when (ID-1)/7=0 then ID end),
[Name0]=max(case when (ID-1)/7=0 then Name end),
[ID1]=max(case when (ID-1)/7=1 then rtrim(ID) else '' end),
[Name1]=max(case when (ID-1)/7=1 then Name else '' end)
from
#T
group by (ID-1)%7ID0 Name0 ID1 Name1
----------- ---------- ------------ ----------
1 10001 8 10009
2 10002 9 10010
3 10004 10 10011
4 10005 11 10012
5 10006
6 10007
7 10008
insert into tb values(1 ,'test')
insert into tb values(2 ,'test')
insert into tb values(3 ,'test')
insert into tb values(4 ,'test')
insert into tb values(5 ,'test')
insert into tb values(6 ,'test')
insert into tb values(7 ,'test')
insert into tb values(8 ,'test')
insert into tb values(9 ,'test')
insert into tb values(10,'test') select m.a,m.b , n.a a1 , n.b b1 from
(select * , (a-1)/7 px1, (a-1)%7 px2 from tb where (a-1)/7 = 0) m
left join
(select * , (a-1)/7 px1, (a-1)%7 px2 from tb where (a-1)/7 = 1) n
on m.px2 = n.px2drop table tb/*
a b a1 b1
----------- ---------- ----------- ----------
1 test 8 test
2 test 9 test
3 test 10 test
4 test NULL NULL
5 test NULL NULL
6 test NULL NULL
7 test NULL NULL(所影响的行数为 7 行)*/
create table tb(No int,Card_id varchar(10))
insert into tb values(1 ,'10001')
insert into tb values(2 ,'10002')
insert into tb values(3 ,'10004')
insert into tb values(4 ,'10005')
insert into tb values(5 ,'10006')
insert into tb values(6 ,'10007')
insert into tb values(7 ,'10008')
insert into tb values(8 ,'10009')
insert into tb values(9 ,'10010')
insert into tb values(10,'10011')
insert into tb values(11,'10012')
go select no= max(case when (no-1)/7=0 then no end),
card_id=max(case when (no-1)/7=0 then card_id end),
no1=max(case when (no-1)/7=1 then no else '' end),
card_id1=max(case when (no-1)/7=1 then card_Id else '' end)
from tb
group by (no-1)%7no card_id no1 card_id1
----------- ---------- ------------ ----------
1 10001 8 10009
2 10002 9 10010
3 10004 10 10011
4 10005 11 10012
5 10006
6 10007
7 10008
警告: 聚合或其他 SET 操作消除了空值。(7 行受影响)
数据表 A,字段分别为 a(主键),b 数据显示如下:(sql2000数据库)
a ¦ b
1 ¦ test
2 ¦ test
3 ¦ test
4 ¦ test
5 ¦ test
6 ¦ test
7 ¦ test
8 ¦ test
9 ¦ test
10 ¦ test
我想取出 字段a的前7行,和最后三行组合成一个新的数据集,如下:
a ¦ b ¦ a1 ¦ b1
1 ¦ test ¦ 2 ¦ test
3 ¦ test ¦ 4 ¦ test
5 ¦ test ¦ 6 ¦ test
7 ¦ test ¦ 8 ¦
9 ¦ test ¦ 10 ¦
这样的sql语句要如何实现呀?
谢谢各位指教