問題描述

Paul 為下面的問題提供了一個有用的腳本，但我想實際影響這些更改.如果我使用select語句，我只能看到它請幫忙解決這個問題

Paul provided a useful script for the issue below but I would like to actually effect the changes. I can only see it if I use the select statement Please help with this one

表名:Citizen

Firstname       Lastname    Telephone         Many other columns......
John             Smith      03907625212    
Andrew           Evans      0807452132    
Bill             Towny      05907122139  
Dame             Beaut      07894650569   

我有超過 150,000 條記錄，其中電話號碼需要采用設置格式(設置電話區號和遞增順序)，即 01907000001、01907000002，如下所示.除了名字和姓氏之外，還有其他列都將保持不變.只有電話字段需要此轉換.

I have over 150,000 records where the Telephone number needs to be adopted to a set format (set telephone area code and in incremental order) ie 01907000001, 01907000002 as shown below. There are other columns asides the firstname and lastname which will all remain unchanged..only telephone field requires this transformation.

理想情況下應該是這樣的:

It should ideally look like this:

Firstname       Lastname      Telephone         Many other columns......
John             Smith       01907000001   
Andrew           Evans       01907000002  
Bill             Towny       01907000003 
Dame             Beaut       01907000004   

我將非常感謝您在這方面的幫助或指導.

I will really appreciate some help or guidance on this.

推薦答案

試試這個:

SELECT 
    [FirstName],
    [LastName],
    '01907' + --area code
    RIGHT('00000' + --middle padding for zero's
        CAST(ROW_NUMBER() OVER (ORDER BY [LastName]) AS VARCHAR) --incremental value
        ,6) AS 'Telephone' --6 characters plus area code

        --<< YOUR OTHER FIELDS
FROM 
    [AdventureWorks].[Person].[Person]

我使用 Adventure Works 只是為了測試它.

I used Adventure Works just to test it.

如果您希望它增加其他內容，請更改窗口函數中的 ORDER BY 子句.

Change the ORDER BY clause in the windowing function if you want it to increment by something else.

這篇關于用于增量創建記錄的腳本的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！