SQL分頁查詢的寫法總結-MySQL、SQL Server、Oracle

在平時工作中，我遇到sqlserver版本支援的語句有差別，因為有的時候回使用mysql，mysql 判斷語句比較簡單，而已可以使用到sqlserver的2016的版本，比如：

mysql一個判斷存儲過程的語句：

DROP PROCEDURE IF EXISTS procedure_test;

該語句可以同樣适用于sqlserver的2016版本，但是将語句運作在sqlserver的2014版本就會提示‘IF’附近有文法錯誤，是以說sqlserver版本不同支援的語句也有不同。

是以為了統一适用所有的版本，我們要适用sqlserver自帶的文法格式，這樣也是防止出錯，下面我将舉例我們常用的：

1、判斷存儲過程是否存在

IF EXISTS （SELECT *FROM SYSOBJECTS WHERE NAME =‘存儲過程名稱’ AND TYPE = ‘P’）

2、判斷函數是否存在

IF EXISTS （SELECT *FROM SYSOBJECTS WHERE NAME =‘函數名稱’ AND TYPE = ‘FN’）

2、判斷觸發器是否存在

IF EXISTS （SELECT *FROM SYSOBJECTS WHERE ID = OBJECT_ID(N’[dbo].[觸發器名稱]’) AND OBJECTPROPERTY（ID，N’IsTrigger’）=1）

3、判斷資料庫是否存在

IF EXISTS （SELECT *FROM MASTER…SYSDATABASES WHERE NAME = ‘資料庫名’）

4、判斷表是否存在

IF EXISTS （SELECT *FROM SYSOBJECTS WHERE ID = N ‘表名’ AND OBJECTPROPERTY（ID，N’IsUserTable’）=1）

以上該語句可以支援所有sqlserver的版本。

————————————————

版權聲明：本文為CSDN部落客「探索.求知者」的原創文章，遵循CC 4.0 BY-SA版權協定，轉載請附上原文出處連結及本聲明。

原文連結：https://blog.csdn.net/fushideng5027/article/details/100891181

寫法三：利用 ORDER BY 子句 OFFSET/FETCH

适用于：SQL Server 2012 (11.x) 及更高版本和 Azure SQL 資料庫。

OFFSET 和 FETCH 文法：

-- Syntax for SQL Server and Azure SQL Database  

ORDER BY order_by_expression  

    [ COLLATE collation_name ]   

    [ ASC | DESC ]   

    [ ,...n ]   

[ <offset_fetch> ]  

<offset_fetch> ::=  

{   

    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }  

    [  

      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY  

    ]  

}

1，OFFSET 指定開始從查詢表達式傳回行之前跳過的行數。 該值可以是大于或等于零的整數常量或表達式

2，FETCH  指定在處理 OFFSET 子句後傳回的行數。 該值可以是大于或等于 1 的整數常量或表達式。

3，要有ORDER BY 沒有ORDER BY無法查詢到結果

寫法實作：

-- 分頁查詢 通用語句

SELECT * FROM TABLE 

ORDER BY ID

OFFSET ( pageIndex - 1 ) * pageSize ROWS 

FETCH NEXT pageIndex * pageSize  ROWS ONLY

-- 第一頁 每頁100條

SELECT * FROM EMP ORDER BY ID OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

-- 第二頁 每頁100條

SELECT * FROM EMP ORDER BY ID OFFSET 100 ROWS FETCH NEXT 200 ROWS ONLY 

-- 第801頁 每頁100條

SELECT * FROM EMP ORDER BY ID OFFSET 80000 ROWS FETCH NEXT 80100 ROWS ONLY 

提示：

SQL Sever限制行數推薦使用TOP（或 OFFSET 和 FETCH）：

使用 TOP（或 OFFSET 和 FETCH）而非 SET ROWCOUNT 限制傳回的行數。 這些方法之是以優于使用 SET ROWCOUNT，原因包括以下各項：

作為 SELECT 語句的一部分，查詢優化器在查詢優化期間可能會考慮 TOP 或 FETCH 子句中 expression 的值。 由于在運作查詢的語句外部使用 SET ROWCOUNT，不會在查詢計劃中考慮它的值。

————————————————

版權聲明：本文為CSDN部落客「一地紅塵」的原創文章，遵循CC 4.0 BY-SA版權協定，轉載請附上原文出處連結及本聲明。

原文連結：https://blog.csdn.net/JavaAndLI/article/details/125359786

SqlServer建立存儲過程

TOP用法:

IF EXISTS (SELECT *FROM SYSOBJECTS WHERE NAME ='Proc_TopPage_Demo2' AND TYPE = 'P')
    DROP PROC Proc_TopPage_Demo2
GO 
CREATE PROCEDURE Proc_TopPage_Demo2
@pageIndex INT=1,
@pageSize INT=10
AS
BEGIN
DECLARE @sql NVARCHAR(1000)='';
set @sql='SELECT TOP '+CONVERT(varchar,@pageSize)+' *FROM( SELECT row_number() OVER(ORDER BY id) AS rowNum, * FROM MOCK_DATA ) AS t WHERE t.rowNum>'+CONVERT(varchar,@pageSize*(@pageIndex-1))
END 
           

ROW_NumBer:

CREATE PROCEDURE Proc_TopRowNumber_Demo
@pageIndex INT=1,
@pageSize INT=10
AS
SELECT *FROM (SELECT row_number() OVER(ORDER BY id) AS rowNum ,* FROM MOCK_DATA) AS T
WHERE T.rowNum>(@pageIndex-1)*@pageSize AND T.rowNum <=(@pageIndex*@pageSize)
go