每日一SQL-重新編號

每日一SQL-重新編號

由於這幾個月都在用Linq to SQL,以至於對 T-SQL語法陌生到不行。

但到底操作資料庫的還是SQL語法,所以這種基本功還是不能忘比較好。

為了達到這個目標,就下定決心來個每日一SQL,不管是select、insert、或是內建函數都好

每天熟悉一種,一年就有365種,應該綽綽有餘了吧。

今天剛好有個需求必須重新排一個 nvarchar型別的序號,所以就從這個開始吧

首先先建一個資料表"重新排序",欄位如下

db787128ecb047a48bb5294d8f746fc4

先來紀錄一個上次別人教我的,如果要從不同資料庫或是不同table,批次update或insert資料進來

可以這樣寫


INSERT INTO [My].[dbo].[重新排序]

([編號],[類別],[標題])

select '00000000',CategoryName,Description 

from Northwind.dbo.Categories

只要欄位數量跟型別有對好,就會把後面那個select資料全部inset到前面的table(當然也可以下where條件)

結果( ID的欄位我有給預設值 newid()  )

374d6b4aa5ec43ee9fdd4d0644dd17a7

好了之後

假設我想以類別做排序,然後依序編號的話

首先要學的是怎麼編號


select *
, row_number() over(order by [類別]) as rnk
from [My].[dbo].[重新排序]

 

row_number()這個function還滿好用的,也可以用來做分頁(不過自從有了Linq我就沒用過了)

排序函數:ROW_NUMBER()、RANK() 與 DENSE_RANK()

會了之後,但還需要一樣東西,因為我想要我的序號是八位數

不足的前面要補0,所以必須用其他的方法,好巧不巧保哥的部落格前幾天才發了一篇

T-SQL 技巧:產生「固定長度前面補零的數字字串」

定期觀看大師的部落格果然只有好處沒有壞處,需要用到的時候就知道好用

於是乎直接挑第三個範例來用,再稍微結合上面的語法


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,再從右邊取八位數

結果就是這樣

3f6ad223bd844bbbb620cb50eba9353f

order by之後看比較清楚

ebd08a5a1ede4e83a51ab113fd7d82d5

 

這樣就解決啦。又學到一樣東西。