MS SQL SERVER 大数据量、多表查询、分页显示的SQL语句如何写

MS SQL SERVER 大数据量、多表查询、分页显示的SQL语句怎么写？
MS SQL SERVER 中建了一个数据库，有一个主表和几个附表，主表的数据量很大，百万级别的。主表通过几个字段和附表关联，做报表的时候需要同时查询主表、附表才能组成一条完整的记录。

主表为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语句该怎么写呢？

------解决方案--------------------



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字段一样建立聚集索引

------解决方案--------------------
这种多表关联查询你肯定会，直接用左联接就可以了，即使附表为空也可以显示出来，主要跟你讲下怎么样分页。这个需要通过存储过程来实现。



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

------解决方案--------------------
那就用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
------解决方案--------------------

引用:

如果主表mainTable还有recordDate和recordTime两个字段，根据recordDate或者recordTime检索，mainTable的ID可能是不连续的应该怎么处理呢？能取查询结果的xxx行至yyy行的记录吗？



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

MS SQL SERVER 大数据量、多表查询、分页显示的SQL语句如何写

相关推荐