主表为mainTable,附表为table1,table2,table3...,主表mainTable包含ID、table1-ID、table2-ID、table3-ID,table1包含两个字段table1-ID,table1-Name,table2,table3表结构和table1类似。
mainTable中table1-ID,table2-ID,table3-ID其中的一项或者两项可能为空,现在要查询mainTable,要求查询的结果如下:
ID table1-Name table2-Name table3-Name
为了增强用户体验,采用分页显示,每次取几十条记录,像这种SQL语句该怎么写呢?
9 个解决方案
#1
select
m.id,a.name as table1-name,b.name as table2-name,c.name as table3-name
from maintable m
inner join table1 a on m.table1-ID=a.table1-ID
inner join table2 b on m.table2-ID=b.table1-ID
inner join table3 c on m.table3-ID=c.table1-ID
where m.id between m and n
在maintable的id字段建立聚集索引,table1-ID-table3-ID建立非聚集索引table1-table3的id字段一样建立聚集索引
#2
这种多表关联查询你肯定会,直接用左联接就可以了,即使附表为空也可以显示出来,主要跟你讲下怎么样分页。这个需要通过存储过程来实现。
CREATE PROCEDURE [存储过程名]
@page_size INT = 50,
@page_index INT = 1
AS
BEGIN
declare @page_start INT
declare @page_end INT
SET @page_start = @page_size * (@page_index - 1) + 1
SET @page_end = @page_size + @page_start - 1
select * from mainTable t
WHERE t.ID between @page_start and @page_end
order by t.ID
END
#3
如果主表mainTable还有recordDate和recordTime两个字段,根据recordDate或者recordTime检索,mainTable的ID可能是不连续的应该怎么处理呢?能取查询结果的xxx行至yyy行的记录吗?
#4
用存储过程也不错的,如果主表mainTable还有recordDate和recordTime两个字段,根据recordDate或者recordTime检索,mainTable的ID可能是不连续的,这时应该怎么写呢?能否将查询的结果加上行号呢?根据行号来提取xxx行至yyy行的记录。
#5
是啊,如果你的ID是不连续的,那么肯定要加上行号。
select * from
(
select *,ROW_NUMBER() over(order by ID) as rn from mainTable
) t
WHERE t.rn between @page_start and @page_end
order by t.rn
#6
那就用row_number()函数分页了,注意在排序列上建立聚集索引,maintable 的关联ID上建立非聚集索引
子表的ID上建立聚集索引,性能不会太差的
select * from
(
select row_number()over(order by createtime ) as rownum,
m.id,a.name as table1-name,b.name as table2-name,c.name as table3-name
from maintable m
inner join table1 a on m.table1-ID=a.table1-ID
inner join table2 b on m.table2-ID=b.table1-ID
inner join table3 c on m.table3-ID=c.table1-ID
) where rownum>10 and rownum<=20
子表的ID上建立聚集索引,性能不会太差的
select * from
(
select row_number()over(order by createtime ) as rownum,
m.id,a.name as table1-name,b.name as table2-name,c.name as table3-name
from maintable m
inner join table1 a on m.table1-ID=a.table1-ID
inner join table2 b on m.table2-ID=b.table1-ID
inner join table3 c on m.table3-ID=c.table1-ID
) where rownum>10 and rownum<=20
#7
create proc [存储过程名]
(@recordDate [recordDate字段数据类型], --> 查询参数1
@recordTime [recordTime字段数据类型] --> 查询参数2
@xxx int, --> 提取的起始行号
@yyy int --> 提取的结束行号
)
as
begin
select ID,[table1-Name],[table2-Name],[table3-Name] from
(select a.ID,t1.[table1-Name],t2.[table2-Name],t3.[table3-Name],
row_number() over(order by getdate()) 'rn'
from mainTable a
left join table1 t1 on a.[table1-ID]=t1.ID
left join table2 t2 on a.[table2-ID]=t2.ID
left join table3 t3 on a.[table3-ID]=t3.ID
where a.recordDate=@recordDate and a.recordTime=@recordTime) t
where rn between @xxx and @yyy
end
#8
像数据量比较大的数据库,一般有下面几种处理方式:
1 一次把所有符合条件的记录检索出来放入内存,不考虑内存容量限制。显示的时候,移动游标的位置来定位,游标移动的速度是很慢的,如果采用分页显示,效果还能接受,翻页的时候基本都要等待的,如果使用VirtualList来显示,拖动滚动条的时候由于记录集游标的速度跟不上滚动条的速度,会很卡。
2 从检索的记录中每次取几十条,分页显示。这种方法每次翻页都要从海量数据里面重新检索,数据库会做很多重复的工作,效果和效率有待确认。
方法1对于海量数据是不可行的,内存容量就是个问题,对于相对小的数据量,不用游标定位,还有没有别的方法?
方法2效率应该比较低,内存占用不大,服务器的压力会比较大,有没有办法优化?
大数据量高效检索和显示的问题,除了上述两种方法,还有没有更好的方法?
1 一次把所有符合条件的记录检索出来放入内存,不考虑内存容量限制。显示的时候,移动游标的位置来定位,游标移动的速度是很慢的,如果采用分页显示,效果还能接受,翻页的时候基本都要等待的,如果使用VirtualList来显示,拖动滚动条的时候由于记录集游标的速度跟不上滚动条的速度,会很卡。
2 从检索的记录中每次取几十条,分页显示。这种方法每次翻页都要从海量数据里面重新检索,数据库会做很多重复的工作,效果和效率有待确认。
方法1对于海量数据是不可行的,内存容量就是个问题,对于相对小的数据量,不用游标定位,还有没有别的方法?
方法2效率应该比较低,内存占用不大,服务器的压力会比较大,有没有办法优化?
大数据量高效检索和显示的问题,除了上述两种方法,还有没有更好的方法?
#9
按照几位给的SQL语句可以用,速度不太理想,还需要调整,结贴
#1
select
m.id,a.name as table1-name,b.name as table2-name,c.name as table3-name
from maintable m
inner join table1 a on m.table1-ID=a.table1-ID
inner join table2 b on m.table2-ID=b.table1-ID
inner join table3 c on m.table3-ID=c.table1-ID
where m.id between m and n
在maintable的id字段建立聚集索引,table1-ID-table3-ID建立非聚集索引table1-table3的id字段一样建立聚集索引
#2
这种多表关联查询你肯定会,直接用左联接就可以了,即使附表为空也可以显示出来,主要跟你讲下怎么样分页。这个需要通过存储过程来实现。
CREATE PROCEDURE [存储过程名]
@page_size INT = 50,
@page_index INT = 1
AS
BEGIN
declare @page_start INT
declare @page_end INT
SET @page_start = @page_size * (@page_index - 1) + 1
SET @page_end = @page_size + @page_start - 1
select * from mainTable t
WHERE t.ID between @page_start and @page_end
order by t.ID
END
#3
如果主表mainTable还有recordDate和recordTime两个字段,根据recordDate或者recordTime检索,mainTable的ID可能是不连续的应该怎么处理呢?能取查询结果的xxx行至yyy行的记录吗?
#4
用存储过程也不错的,如果主表mainTable还有recordDate和recordTime两个字段,根据recordDate或者recordTime检索,mainTable的ID可能是不连续的,这时应该怎么写呢?能否将查询的结果加上行号呢?根据行号来提取xxx行至yyy行的记录。
#5
是啊,如果你的ID是不连续的,那么肯定要加上行号。
select * from
(
select *,ROW_NUMBER() over(order by ID) as rn from mainTable
) t
WHERE t.rn between @page_start and @page_end
order by t.rn
#6
那就用row_number()函数分页了,注意在排序列上建立聚集索引,maintable 的关联ID上建立非聚集索引
子表的ID上建立聚集索引,性能不会太差的
select * from
(
select row_number()over(order by createtime ) as rownum,
m.id,a.name as table1-name,b.name as table2-name,c.name as table3-name
from maintable m
inner join table1 a on m.table1-ID=a.table1-ID
inner join table2 b on m.table2-ID=b.table1-ID
inner join table3 c on m.table3-ID=c.table1-ID
) where rownum>10 and rownum<=20
子表的ID上建立聚集索引,性能不会太差的
select * from
(
select row_number()over(order by createtime ) as rownum,
m.id,a.name as table1-name,b.name as table2-name,c.name as table3-name
from maintable m
inner join table1 a on m.table1-ID=a.table1-ID
inner join table2 b on m.table2-ID=b.table1-ID
inner join table3 c on m.table3-ID=c.table1-ID
) where rownum>10 and rownum<=20
#7
create proc [存储过程名]
(@recordDate [recordDate字段数据类型], --> 查询参数1
@recordTime [recordTime字段数据类型] --> 查询参数2
@xxx int, --> 提取的起始行号
@yyy int --> 提取的结束行号
)
as
begin
select ID,[table1-Name],[table2-Name],[table3-Name] from
(select a.ID,t1.[table1-Name],t2.[table2-Name],t3.[table3-Name],
row_number() over(order by getdate()) 'rn'
from mainTable a
left join table1 t1 on a.[table1-ID]=t1.ID
left join table2 t2 on a.[table2-ID]=t2.ID
left join table3 t3 on a.[table3-ID]=t3.ID
where a.recordDate=@recordDate and a.recordTime=@recordTime) t
where rn between @xxx and @yyy
end
#8
像数据量比较大的数据库,一般有下面几种处理方式:
1 一次把所有符合条件的记录检索出来放入内存,不考虑内存容量限制。显示的时候,移动游标的位置来定位,游标移动的速度是很慢的,如果采用分页显示,效果还能接受,翻页的时候基本都要等待的,如果使用VirtualList来显示,拖动滚动条的时候由于记录集游标的速度跟不上滚动条的速度,会很卡。
2 从检索的记录中每次取几十条,分页显示。这种方法每次翻页都要从海量数据里面重新检索,数据库会做很多重复的工作,效果和效率有待确认。
方法1对于海量数据是不可行的,内存容量就是个问题,对于相对小的数据量,不用游标定位,还有没有别的方法?
方法2效率应该比较低,内存占用不大,服务器的压力会比较大,有没有办法优化?
大数据量高效检索和显示的问题,除了上述两种方法,还有没有更好的方法?
1 一次把所有符合条件的记录检索出来放入内存,不考虑内存容量限制。显示的时候,移动游标的位置来定位,游标移动的速度是很慢的,如果采用分页显示,效果还能接受,翻页的时候基本都要等待的,如果使用VirtualList来显示,拖动滚动条的时候由于记录集游标的速度跟不上滚动条的速度,会很卡。
2 从检索的记录中每次取几十条,分页显示。这种方法每次翻页都要从海量数据里面重新检索,数据库会做很多重复的工作,效果和效率有待确认。
方法1对于海量数据是不可行的,内存容量就是个问题,对于相对小的数据量,不用游标定位,还有没有别的方法?
方法2效率应该比较低,内存占用不大,服务器的压力会比较大,有没有办法优化?
大数据量高效检索和显示的问题,除了上述两种方法,还有没有更好的方法?
#9
按照几位给的SQL语句可以用,速度不太理想,还需要调整,结贴