ALTER procedure [dbo].[p_q_GroupToHot]
(
@top int,
@page int
)
as
BEGIN
declare @sqlstr varchar(max)
if @page<2
begin
set @sqlstr='select top '+str(@top)+'
group_id groupid,
group_title grouptitle,
(
select
[user_name]
from
user_info
where
[user_id] =
(
select
group_member_userid
from
group_member
where
group_member_type = 3 and
group_member_groupid = group_id
)
) as creatorname,
(
select
count(group_member_id)
from
group_member
where
group_member_groupid = group_id and
group_member_type <> 0
) as total,--这是查询总人数
group_intro groupintro,
group_time grouptime,
group_sign groupsign
from
group_info
order by
total desc'
end
else
begin
set @sqlstr='select top '+str(@top)+'
group_id groupid,
group_title grouptitle,
(
select
[user_name]
from
user_info
where
[user_id] =
(
select
group_member_userid
from
group_member
where
group_member_type = 3 and
group_member_groupid = group_id
)
) as creatorname,
(
select
count(group_member_id)
from
group_member
where
group_member_groupid = group_id and
group_member_type <> 0
) as total,
group_intro groupintro,
group_time grouptime,
group_sign groupsign
from
group_info
where group_id < (
select min(group_id)
from (
select top '+str((@page-1)*@top)+' group_id
from group_info
order by group_id desc
) as tmp
)
order by total desc'--按总人数降序排
end
print(@sqlstr)
exec(@sqlstr)
END
我用的是sql2005
这是两张表,结合上面的提问,首先,表1的oneid和表2的oneid是主外键关系
第一,我要查询出表1 oneid的用户数量,查询出来后,用分页存储过程按用户的数量降序排列,做好后,调用,显示第二页时就会出现问题,第一行和第一页里的第一行是同一条数据,不知道能明白我说的意思吗?
table表1
oneid name
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8
table表2
id oneid 用户名name
1 1 m
2 1 n
3 2 k
4 3 o
5 5 n
6 7 k
7 5 uu
8 4 ii
9 1 uy
10 5 yejd