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

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

 

歇菜截图

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

一个是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