每日一SQL-重新編號
由於這幾個月都在用Linq to SQL,以至於對 T-SQL語法陌生到不行。
但到底操作資料庫的還是SQL語法,所以這種基本功還是不能忘比較好。
為了達到這個目標,就下定決心來個每日一SQL,不管是select、insert、或是內建函數都好
每天熟悉一種,一年就有365種,應該綽綽有餘了吧。
今天剛好有個需求必須重新排一個 nvarchar型別的序號,所以就從這個開始吧
首先先建一個資料表"重新排序",欄位如下
先來紀錄一個上次別人教我的,如果要從不同資料庫或是不同table,批次update或insert資料進來
可以這樣寫
INSERT INTO [My].[dbo].[重新排序]
([編號],[類別],[標題])
select '00000000',CategoryName,Description
from Northwind.dbo.Categories
只要欄位數量跟型別有對好,就會把後面那個select資料全部inset到前面的table(當然也可以下where條件)
結果( ID的欄位我有給預設值 newid() )
好了之後
假設我想以類別做排序,然後依序編號的話
首先要學的是怎麼編號
select *
, row_number() over(order by [類別]) as rnk
from [My].[dbo].[重新排序]
row_number()這個function還滿好用的,也可以用來做分頁(不過自從有了Linq我就沒用過了)
排序函數:ROW_NUMBER()、RANK() 與 DENSE_RANK()
會了之後,但還需要一樣東西,因為我想要我的序號是八位數
不足的前面要補0,所以必須用其他的方法,好巧不巧保哥的部落格前幾天才發了一篇
定期觀看大師的部落格果然只有好處沒有壞處,需要用到的時候就知道好用
於是乎直接挑第三個範例來用,再稍微結合上面的語法
with 暫時table
as
(
select *
, row_number() over(order by [類別]) as rnk
from [My].[dbo].[重新排序]
)
update 暫時table
set [編號] = RIGHT( REPLICATE('0', 8) + CAST(rnk as NVARCHAR), 8)
基本上就是select 重新排序這個table加上一個按類別排序的編號rnk,在給一個暫時的table名稱
接著update他,再update時,將rnk轉成字串,加上replicate建立一個八位數的0,再從右邊取八位數
結果就是這樣
order by之後看比較清楚
這樣就解決啦。又學到一樣東西。