前几天刚写的,对多个主键 多表 多个字段组合查询 多字段排序的情况下实现真分页的存储过程:
CREATE
procedure
TEACHERS_SEARCH
@schoolid
as
int
=
0
,
@teacher_name
as
nvarchar
(
50
)
=
''
,
@push_status
as
tinyint
=
0
,
@count
as
tinyint
=
0
,
--
真分页统计
@pageSize
as
int
=
10
,
--
当前页需要显示的记录数
@topSize
as
int
=
10
--
包括该页前面的记录总数
as
--
@status 0：全部;1：在课件区;2:在blog区;3:既在课件区又在blog区;
declare
@str
nvarchar
(
2000
)
declare
@str_search
nvarchar
(
1000
)
set
@str_search
=
'
1=1
'
if
(
@schoolid
<>
0
)
set
@str_search
=
@str_search
+
'
and teacher.schoolid=
'
+
str
(
@schoolid
)
if
(
@teacher_name
<>
''
)
set
@str_search
=
@str_search
+
'
and teacher.name like
'''
+
@teacher_name
+
'
%
'''
if
(
@push_status
=
1
)
set
@str_search
=
@str_search
+
'
and TeacherRecommand.status in (1,3)
'
if
(
@push_status
=
2
)
set
@str_search
=
@str_search
+
'
and TeacherRecommand.status in (2,3)
'
if
(
@push_status
=
3
)
set
@str_search
=
@str_search
+
'
and TeacherRecommand.status in (1,2)
'
if
(
@count
=
0
)
set
@str
=
'
select * from (SELECT top
'
+
str
(
@pageSize
)
+
'
* from ( select top
'
+
str
(
@topSize
)
+
'
Teacher.schoolid,Teacher.teacherid,teacher.name as teacher_name,school.name as school_name,teacher.email,teacher.telephone, TeacherRecommand.status,School.rainbowsite as rainbowsite
FROM Teacher INNER JOIN
School ON Teacher.schoolid = School.schoolid
LEFT OUTER JOIN
TeacherRecommand ON Teacher.schoolid = TeacherRecommand.schoolid AND
Teacher.teacherid = TeacherRecommand.teacherid
WHERE
'
+
@str_search
+
'
order by teacher.schoolid desc,teacher.teacherid desc) a
order by schoolid asc,teacherid asc) b
order by schoolid desc,teacherid desc
'
else
set
@str
=
'
SELECT count(*)
FROM Teacher INNER JOIN
School ON Teacher.schoolid = School.schoolid
LEFT OUTER JOIN
TeacherRecommand ON Teacher.schoolid = TeacherRecommand.schoolid AND
Teacher.teacherid = TeacherRecommand.teacherid
WHERE
'
+
@str_search
--
print @str
exec
(
@str
)
GO
110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
