表
id type
1 4
2 2
3 5
4 1
5 5
6 4
数据随机的。第一次显示1-3条。数据type=5为止。第二页 4-5行记录 也是type=5 .第三页如果后面没有类型等于5的。就显示全部。如果连续5.就显示一条数据。不知道我描述清楚没。。
id type
1 4
2 2
3 5
4 1
5 5
6 4
数据随机的。第一次显示1-3条。数据type=5为止。第二页 4-5行记录 也是type=5 .第三页如果后面没有类型等于5的。就显示全部。如果连续5.就显示一条数据。不知道我描述清楚没。。
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,type int)
insert into [tb]
select 1,4 union all
select 2,2 union all
select 3,5 union all
select 4,1 union all
select 5,5 union all
select 6,4
go--存储过程
create proc sp_wsp
@pageindex int
as
if not exists(select * from (select *,pc=(select count(*)+1 from tb where type=5 and id<a.id) from tb a)a where pc=@pageindex and type=5)
select * from tb
else
select id,type from (select *,pc=(select count(*)+1 from tb where type=5 and id<a.id) from tb a)a where pc=@pageindex
go--测试
exec sp_wsp 1
/*结果:
id type
----------- -----------
1 4
2 2
3 5
*/
exec sp_wsp 2
/*结果:
id type
----------- -----------
4 1
5 5
*/
exec sp_wsp 3
/*结果:
id type
----------- -----------
1 4
2 2
3 5
4 1
5 5
6 4
*/
;WITH CTE AS(
SELECT id, SN = ROW_NUMBER() OVER(ORDER BY id) FROM #tb WHERE type = 5
UNION ALL
SELECT MIN(id)-1, SN = 0 FROM #tb
),
CTE2 AS(
SELECT pagenum = a.SN+1, id_start = a.id + 1, id_end = ISNULL(b.id,99999)
FROM CTE a
LEFT JOIN CTE b
ON a.SN + 1 = b.SN
)
SELECT *
FROM #tb t
INNER JOIN CTE2 c
ON t.id BETWEEN c.id_start AND c.id_end
--WHERE c.pagenum = @pagenum
ORDER BY t.id
差不多了。只是我数据不一定。不知道有多少个5.我想从页面取得一个存储过程条件。假如条件等于3
执行exec sp_wsp 3的时候 就显示此数据中最后一条数据
--那就更简单了。
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,type int)
insert into [tb]
select 1,4 union all
select 2,2 union all
select 3,5 union all
select 4,1 union all
select 5,5 union all
select 6,4
go--存储过程
create proc sp_wsp
@pageindex int
as
select id,type from (select *,pc=(select count(*)+1 from tb where type=5 and id<a.id) from tb a)a where pc=@pageindex
go
--
exec sp_wsp 3
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,type int)
insert into [tb]
select 123,4 union all
select 3,2 union all
select 52,5 union all
select 6,1 union all
select 87,5 union all
select 111,4
go--存储过程
create proc sp_wsp
@pageindex int
as
select pp=identity(int,1,1),* into # from tb
select id,type from (select *,pc=(select count(*)+1 from # where type=5 and pp<a.pp) from # a)a where pc=3
go
--
exec sp_wsp 3
create proc sp_wsp
@pageindex int
as
select pp=identity(int,1,1),* into # from tb
select id,type from (select *,pc=(select count(*)+1 from # where type=5 and pp<a.pp) from # a)a where pc=@pageindexgo
--
--不能插入颜色噢,最终版本。
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,type int)
insert into [tb]
select 123,4 union all
select 3,2 union all
select 52,5 union all
select 6,1 union all
select 87,5 union all
select 111,4
go--存储过程
create proc sp_wsp
@pageindex int
as
select pp=identity(int,1,1),* into # from tb
select id,type from (select *,pc=(select count(*)+1 from # where type=5 and pp<a.pp) from # a)a where pc=@pageindex
go
随机的取1行