关于mybatis+sqlserver的多表关联的分页（注意要先分页在多
表关联）
在这⾥浅谈下mybatis+sqlserver 遇到的⼩坑，当我们在l 中写sql 语句取参的时候注意不能⽤#{}去取值
top 关键字是不⽀持的然后会报java.sql.SQLException: '@P0' 附近有语法错误只能⽤${}去取值
SELECT * FROM(
SELECT TOP ${pageSize}* FROM st_student s
WHERE s.stu_id NOT IN (
SELECT TOP ${pageId} s.stu_id FROM st_student s ORDER BY s.stu_id ASC
)ORDER BY s.stu_id ASC
)as a
left join st_stucourse sc
on a.stu_id=sc.stu_id
left join st_course c
__id
-- 准备数据
-- 创建数据库
create database EASTETWO;
use EASTETWO
go
create table st_student(
stu_id int not null primary key IDENTITY(1,1),
stu_name varchar(30),
stu_s int not null,
stu_birth datetime,
stu_birthplace varchar(30),
stu_email varchar(20)
)
go
select*from st_student;
go
set identity_insert st_student ON;
go
insert into st_student (stu_id,stu_name,stu_s,stu_birth,stu_birthplace,stu_email) values(1,'⼩张',1,'2016-12-28','上海','qq@166');
insert into st_student (stu_id,stu_name,stu_s,stu_birth,stu_birthplace,stu_email) values(2,'⼩华',1,'2016-12-27','上海','qq@167');
insert into st_student (stu_id,stu_name,stu_s,stu_birth,stu_birthplace,stu_email) values(3,'⼩明',1,'2016-12-26','上海','qq@168');
insert into st_student (stu_id,stu_name,stu_s,stu_birth,stu_birthplace,stu_email) values(4,'⼩⽂',1,'2016-11-28','上海','qq@169');
insert into st_student (stu_id,stu_name,stu_s,stu_birth,stu_birthplace,stu_email) values(5,'⼩郭',1,'2016-11-26','上海','qq@160');
insert into st_student (stu_id,stu_name,stu_s,stu_birth,stu_birthplace,stu_email) values(6,'⼩刘',1,'2016-10-28','上海','qq@167');
insert into st_student (stu_id,stu_name,stu_s,stu_birth,stu_birthplace,stu_email) values(7,'⼩雯',1,'2016-10-27','上海','qq@167');
go
set identity_insert st_student off;
go
--创建课程表
create table st_course(
co_id int not null primary key IDENTITY(1,1),
co_name varchar(30)
)
go
use EASTETWO;
select*from st_student;
create table st_stucourse(
id int not null primary key IDENTITY(1,1),
stu_id int not null,
co_id int not null,
co_score int not null
)
go
-- 在这⾥注意当插⼊数据的时候⼀定要将 identity_insert 开启
set identity_insert st_stucourse ON
go
insert into st_stucourse (id,stu_id,co_id,co_score) values(10,1,1,90);
insert into st_stucourse (id,stu_id,co_id,co_score) values(11,1,2,70);
insert into st_stucourse (id,stu_id,co_id,co_score) values(12,1,3,88);
insert into st_stucourse (id,stu_id,co_id,co_score) values(13,1,4,88);
insert into st_stucourse (id,stu_id,co_id,co_score) values(14,1,5,98);
insert into st_stucourse (id,stu_id,co_id,co_score) values(6,2,2,88);
insert into st_stucourse (id,stu_id,co_id,co_score) values(7,2,3,90);
insert into st_stucourse (id,stu_id,co_id,co_score) values(8,2,4,99);
insert into st_stucourse (id,stu_id,co_id,co_score) values(9,2,6,88);
-- 执⾏完⼀定要关闭否则在另⼀张表会报错
set identity_insert st_stucourse OFF
go
select*from st_stucourse;
set identity_insert st_course ON
go
insert into st_course (co_id,co_name) values(1,'历史');
go
insert into st_course (co_id,co_name) values(2,'数学');
insert into st_course (co_id,co_name) values(3,'语⽂');
insert into st_course (co_id,co_name) values(4,'英语');
insert into st_course (co_id,co_name) values(5,'地理');
insert into st_course (co_id,co_name) values(6,'政治');
go
set identity_insert st_course OFF
go
go
-- 在实际开发中最好不要⽤*
SELECT*FROM(
SELECT TOP10*FROM st_student s
WHERE s.stu_id NOT IN (
SELECT TOP0 s.stu_id FROM st_student s ORDER BY s.stu_id DESC
) ORDER BY s.stu_id DESC
)as a
left join st_stucourse sc
on a.stu_id=sc.stu_id
left join st_course c
__id
go
-- 遇到的坑就是先分页还是先关联如果你先关联表（如果有⼀对异常多的）那么你在前台页⾯会遇到-- 个问题就是在显⽰第⼀页的数据时会出现显⽰的记录数会少于我们的要求显⽰的记录数
go
SELECT TOP10*FROM st_student s,
st_stucourse sc,
st_course c
where s.stu_id=sc.stu_id __id
and s.stu_id NOT IN
(多表left join
SELECT TOP0 s.stu_id FROM st_student s ORDER BY s.stu_id DESC
)
ORDER BY s.stu_id DESC
go
-- row_number（）会效率更⾼些
use EASTETWO;
select*from (
select top10*
from
(
select row_number() over(order by s.stu_id DESC) as rownumber,*from st_student s
) A
where rownumber >0
)as a
left join st_stucourse sc
on a.stu_id=sc.stu_id
left join st_course c
__id
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论