Sqlserver中分页,2012后支持offset + fetch,2012之前用rownum嵌套查询

今天发现原先用的sql offset fetch好用,换了一个DB就歇菜

Sqlserver中分页,2012后支持offset + fetch,2012之前用rownum嵌套查询

 

歇菜截图

Sqlserver中分页,2012后支持offset + fetch,2012之前用rownum嵌套查询

比较了一下,是数据库版本的问题

一个是13,一个是10

版本低的不支持用offset + fetch 进行分页,mssql2012之后才支持用offset + fetch

所以如果DB版本低了,我就需要改查询语句,改成以下的样子【根据rownum进行排序,再将查询结构查一遍,注意别名前面的原字段名要删除】

本来想着测一下两种分页查询的性能,结果DBA小哥哥瞬间帮我换了个新库,哈哈哈。暂时懒得比较性能了,以后有空直接用哪个jmeter查询比较下。

 

原先的
select bug_id,project_name,cr_name,bug_cr_num,bug_task_num,object_name,bug_description,bug_rca,bug_solution,b.employ_name developer,a.employ_name tester,qa_creationdt,qa_updatedt,bug_deleted_flag,status_des
from qa_buglist
join qa_project on bug_project_id = project_id  
join qa_crtype on cr_id = bug_cr_type_id  
join qa_rtype on object_id = qa_type_id  
join qa_employ a on a.employ_id = qa_tester_id and a.employ_group = 1   
join qa_employ b on b.employ_id= qa_assignee_id and b.employ_group = 2  
join qa_bugstatus on status_id =  bug_status_id
where bug_deleted_flag =0 order by 1 desc offset  0  rows fetch next  20 rows only


更新的
SELECT bug_id,project_name,cr_name,bug_cr_num,bug_task_num,object_name,bug_description,bug_rca,bug_solution, developer, tester,qa_creationdt,qa_updatedt,bug_deleted_flag,status_des FROM (
	SELECT ROW_NUMBER()OVER(ORDER BY bug_id desc) AS rowid,bug_id,project_name,cr_name,bug_cr_num,bug_task_num,object_name,bug_description,bug_rca,bug_solution,b.employ_name developer,a.employ_name tester,qa_creationdt,qa_updatedt,bug_deleted_flag,status_des
from qa_buglist
join qa_project on bug_project_id = project_id  
join qa_crtype on cr_id = bug_cr_type_id  
join qa_rtype on object_id = qa_type_id  
join qa_employ a on a.employ_id = qa_tester_id and a.employ_group = 1   
join qa_employ b on b.employ_id= qa_assignee_id and b.employ_group = 2  
join qa_bugstatus on status_id =  bug_status_id
where bug_deleted_flag =0
) TAB
WHERE rowid BETWEEN 0 AND 20
ORDER BY bug_id desc

  

 

上一篇:buglist系统中涉及的sql MySql版本


下一篇:通过xrdp实现远程桌面连接Windows Azure linux虚拟机