# SQL msSQL 序列函數 ROW_NUMBER() OVER()製作分頁
------
###### tags: `SQL`
[[Database][SQL Server] 序列函數 ROW_NUMBER](https://dog0416.blogspot.com/2018/02/databasesql-server-rownumber.html)
[[筆記][MSSQL]關於OVER子句的使用方式](https://ithelp.ithome.com.tw/articles/10190257)
## 介紹
ROW_NUMBER 函式為顯示分割資料內的資料列的序號,每個分割資料的第一個資料序號從 1 開始。我們透過 PARTITION BY 進行資料分割,其詳細用法如下
在我個人資料庫中的分頁寫法是
舊
```sql=
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (
ORDER BY (case id when '1' then 0 else 1 end) ASC, create_time DESC
) AS RowNum, * FROM [admin_basic]
) AS admin_as WHERE RowNum >= 6 AND RowNum <= 10 ORDER BY RowNum
```
[](https://i.imgur.com/ts9UIKs.png)
![](https://github.com/bestRDJ333/noteImg/raw/main/img/61.png)
### 我的範例1
拆解成以下
```sql=
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (
ORDER BY id ASC
) AS row_id, * FROM [admin_basic]
) AS atabe ORDER BY row_id
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (
ORDER BY (case id when '1' then 0 else 1 end) ASC,
create_time DESC
) AS row_id, * FROM [admin_basic]
) AS atabe ORDER BY row_id
```
-----
#### case than else 筆記
```sql=
SELECT case id
when '1' then 0
else 1
end
Answer
,* FROM [admin_basic] ORDER BY Answer;
```
**同筆記中 case than else**
case then 會做出 0/1分類 然後做 ASC排序
所以只會顯示成
如果有再table中做刪除那id的流水號是不相連號
id 1 0
id 2 1
id 5 1
id 10 1
### 網站範例
再犯例中可以看到
`OVER()` 第一個參數的意思是 分個的單位
所以下方的圖依照分割單位 重新跑rowNumber 編號
[](https://i.imgur.com/tk4D3Tx.png)
![](https://github.com/bestRDJ333/noteImg/raw/main/img/62.png)
### 我的範例1 輸出
#### 新欄位做好了但 id亂掉了!?
以下圖可以看出明明都用 DESC了
ID 怎麼是亂的?
[](https://i.imgur.com/6E3UOMK.png)
![](https://github.com/bestRDJ333/noteImg/raw/main/img/63.png)
### 我的範例2
所以 (如果前後都是 id 排序的話)
id 1 0 rowNumber 1
id 2 1 rowNumber 1
id 5 1 rowNumber 2
id 10 1 rowNumber 3
但第二個參數是createTime
一班使用新增的時間跟id順序是一樣的
但因為此資料庫的資料都時是程式新增所以會變成以下
```sql=
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (
ORDER BY (case id when '1' then 0 else 1 end) ASC,
id DESC /*這邊不同*/
) AS row_id, * FROM [admin_basic]
) AS atabe ORDER BY row_id
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (
ORDER BY (case id when '1' then 0 else 1 end) ASC,
create_time DESC /*這邊不同*/
) AS row_id, * FROM [admin_basic]
) AS atabe ORDER BY row_id
```
### 重要
上方 **依照** id DESC 去排序 所以會重最大id牌到最小id 排序正常
下方 **依照** create_time DESC 去排序
**當欄位內有值相同時!! 會依照當時配給的流水號 去輸出成新排序**
所以
- 新增欄位內值 相同時
- id 排序成 ASC 升序法 (SQL 預設就是ASC 排序)
- 之後再OVER 排成新欄位順序
#### 所以建議依據欄位還是不要有重複會比較好
### 我的範例2 輸出
[](https://i.imgur.com/GgeOGdi.png)
![](https://github.com/bestRDJ333/noteImg/raw/main/img/65.png)
## 搜尋+出第幾筆資料/ 分頁
**aTable 的AS 一定要寫!! 沒用到|不用也要寫**
這段SQL的解釋是這樣的
*不懂往下看截圖
```
SELECT * 從 (
SELECT (分割/新建欄位()
OVER(
ORDER BY (依據id 當id是'1'回傳0其餘回傳1) 由大到小,
id 由大到小 // *這邊看
)
AS row_id, * FROM \[admin_basic]
) AS aTable 搜尋所需要的條件
```
```sql=
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (
ORDER BY (case id when '1' then 0 else 1 end) ASC,
id DESC
) AS row_id, * FROM [admin_basic]
) AS aTable WHERE charindex('t', admin_acc) > 0 AND row_id >= 1 AND row_id <= 10 ORDER BY row_id
```
![](https://i.imgur.com/4eocZ7z.png)
----
看ID 排序就懂了
```sql=
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (
ORDER BY (case id when '1' then 0 else 1 end) ASC,
id DESC
) AS row_id, * FROM [admin_basic]
) AS aTable ORDER BY row_id
```
[](https://i.imgur.com/iV4weMo.png)
![](https://github.com/bestRDJ333/noteImg/raw/main/img/70.png)
---
----
同事寫法
```sql=
/**tony**/
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (
ORDER BY (case id when '1' then 0 else 1 end) ASC, create_time DESC
) AS RowNum, * FROM [admin_basic]
) AS admin_as WHERE RowNum >= 6 AND RowNum <= 10 ORDER BY RowNum
```
![](https://github.com/bestRDJ333/noteImg/raw/main/img/67.png)
![](https://github.com/bestRDJ333/noteImg/raw/main/img/72.png)