[SQL] INSERTED

最近開發需求中，須建立一個主鍵為

uniqueidentifier

型別的表，且當新增一筆資料後須將新建資料的主鍵傳回。由於主鍵不再是

IDENTITY

型態的數值，無法使用

SELECT SCOPE_IDENTITY() AS NewID

方式取得，因此直覺改法就是在執行

INSERT

指令前先透過

NEWID()

產生 GUID，最後再將該筆 GUID 字串

SELECT

回去。


爬了一些網路上的教學後發現：若要將 GUID 作為主鍵，建議使用

NEWSEQUENTIALID()

取代

NEWID()

。不過替換過程是有代價。由於

NEWSEQUENTIALID()

只能使用在

DEFAULT

運算式，無法在預存程序程式碼中產生並指派到參數內，因此若新增資料後要回傳自動透過

NEWSEQUENTIALID()

產生的 GUID 值，需用點小技巧。此時，

INSERTED

指令就派上用場了。

--Table: NewSeqIdDemo

ID                                     FirstName    LastName
-------------------------------------  -----------  -----------
35881e9e-99b8-eb11-80d7-00155d321b03   Felix        Huang
e950efba-99b8-eb11-80d7-00155d321b03   Lanny        Huang

上表中，若執行

INSERT

指令後想取得剛才新增該筆紀錄的特定欄位，可以在

VALUES

的指令前加入

OUTPUT INSERTED.<欄位名稱>

，如：

INSERT INTO NewSeqIdDemo (FirstName, LastName)
OUTPUT INSERTED.FirstName, INSERTED.LastName
VALUES ('Vincent', 'Huang')

這指令很方便，任何欄位值都可以回傳，不過可惜的是無法一一將各欄位取出的值塞進已宣告的參數內。若現在想將 ID 塞進某個已宣告的參數內，方法很簡單，只需要將取出來的欄位值塞進一個資料表內，再透過

SELECT

指令，就可以輕鬆取得並賦值到指定的參數了。

DECLARE @ID AS UNIQUEIDENTIFIER
--宣告一個承接 INSERTED 取出值的臨時資料表
DECLARE @table TABLE (ID UNIQUEIDENTIFIER)

INSERT INTO NewSeqIdDemo (FirstName, LastName)
OUTPUT INSERTED.ID INTO @table
VALUES ('Vincent', 'Huang')

SELECT @ID = ID FROM @table

參考來源：

1. NEWSEQUENTIALID (Transact-SQL)
2. Return the uniqueidentifier generated by a default on insert
3. SQL 下完 Insert Into 之後，取得剛剛 Insert 的欄位值 (指定返回欄位)
   

[SQL] 取得分群 (Group by) 後，最新(大) / 最舊(小) 值

有時在操作 SQL 時，會遇到資料分群 (Group By) 後，取得每個群組最大 (新) 的項目。這裡參考了 Stack overflow 上一些解決方法。主要的概念都須用到子查詢，並在子查詢的操作過程中動一些小手段。

方法一：先利用 Group By 取得 Max 值後，在 Join 原表

SELECT t.Train, t.Dest, r.MaxTime
FROM (
      SELECT Train, MAX(Time) as MaxTime
      FROM TrainTable
      GROUP BY Train
) r
INNER JOIN TrainTable t
ON t.Train = r.Train AND t.Time = r.MaxTime

方法二：替子查詢內容 Group By 並添加流水號，再透過主查詢將需要的內容取出來

SELECT train, dest, time FROM ( 
  SELECT train, dest, time, 
    RANK() OVER (PARTITION BY train ORDER BY time DESC) dest_rank
    FROM traintable
  ) where dest_rank = 1

個人比較偏好方法二，簡潔有力呀!

參考來源：GROUP BY with MAX(DATE)

[DB] 將 Group by 後的內容濃縮至一列，並以逗號分隔每筆群組資料

不知為何，過去總是很抗拒 SQL 的

group by

指令。不過因為接手的幾個案子需要大量使用到 SQL 的聚合函數 (Aggregation, 如:

Sum, Max, Min

...)，因此就得跟它面對了。而這幾個案子中的需求情境，並不是要加總或計算

Select

出來的最大、最小值，而是將資料

group by

後，將各組的每筆資料以單一符號分隔，並濃縮成一列顯示。這是種比較特別的聚合方法，因此就值得紀錄一下啦。

先看看範例

SELECT * FROM CITY;

 C_ID  C_TYPE  C_NAME
-----  ------  ---------
    1  TW      Nantou
    2  TW      Hsinchu
    3  TW      Chiayi
    4  D       Taipei
    5  D       Taichung
    6  TW      Hualian
    7  TW      Taitung
    8  D       Tainan
    9  D       Kaohsiung

需求：

1. 我想要把以上這些城市依照

   C_TYPE

   欄位進行分組
2. 接著要把各組內容濃縮成一列
3. 每筆資料以半形逗號進行分隔

產生的結果：

CITY_TYPE    CITY_ID      CITY_NAME
----------   ----------   -------------------------------------
        D    4,5,8,9      Taipei,Taichung,Tainan,Kaohsiung
       TW    1,2,3,6,7    Nantou,Hsinchu,Chiayi,Hualian,Taitung

當然也可以透過程式，以迴圈的方式處理。不過有些資料處理是資料庫的強項，能在資料庫處理掉的手續，當然就交給資料庫去代勞囉。究竟這需求要怎麼處理呢？由於首次遇到的案子是使用 Oracle 資料庫，而且還是非常骨董級的 9.2i 版，因此就很勤勞的找出 SQL Server 及 Oracle 兩版本的解法 (Opps.... Oracle 各版本寫法又不太一樣) 詳細細節就不多說了，直接看 Code 吧。

使用 Oracle 9.2i

使用

ROW_NUMBER()

及

SYS_CONNECT_BY_PATH

達成。

--ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i
------------------------------------------------------------------
SELECT C_TYPE as CITY_TYPE,
    LTRIM(MAX(SYS_CONNECT_BY_PATH(C_ID,',')) 
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS CITY_ID,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(C_NAME,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS CITY_NAME
FROM   (SELECT C_TYPE,
               C_ID,
               C_NAME,
               --C_Name
               ROW_NUMBER() OVER (PARTITION BY C_TYPE ORDER BY C_ID) AS curr,
               ROW_NUMBER() OVER (PARTITION BY C_TYPE ORDER BY C_ID) -1 AS prev,
               --C_ID               
               ROW_NUMBER() OVER (PARTITION BY C_TYPE ORDER BY C_ID) -1 AS prev2
        FROM   CITY)
GROUP BY C_TYPE
CONNECT BY prev = PRIOR curr AND C_TYPE = PRIOR C_TYPE
START WITH curr = 1;

使用 Oracle 11g Release 2

使用

LISTAGG

達成。

SELECT C_TYPE AS CITY_TYPE,
 LISTAGG(C_ID, ',') WITHIN GROUP (ORDER BY C_ID) AS CITY_ID,
 LISTAGG(C_NAME, ',') WITHIN GROUP (ORDER BY C_ID) AS CITY_NAME
FROM   CITY
GROUP BY C_TYPE;

參考：String Aggregation Techniques

使用 SQL Server 2008 以上版本

運用

XML PATH

來達成。

SELECT C_TYPE AS CITY_TYPE,
       CITY_ID = STUFF(
                         (SELECT ', ' + CAST(C_ID AS VARCHAR)
                          FROM CITY
                          WHERE C_TYPE = x.C_TYPE
                            FOR XML PATH(''), TYPE).value('.[1]', 'varchar(max)'), 1, 2, ''),
       CITY_NAME = STUFF(
                           (SELECT ', ' + C_NAME
                            FROM CITY
                            WHERE C_TYPE = x.C_TYPE
                              FOR XML PATH(''), TYPE).value('.[1]', 'varchar(max)'), 1, 2, '')
FROM CITY AS x
GROUP BY C_TYPE;

參考：How to use COALESCE with multiple rows and without preceding comma?

使用 SQL Server 2017 以上版本

(2018-12-12 補充)
偶然間在網路文章中發現有人提出使用這個版本才支援的原生函式

STRING_AGG

，微軟真佛心，這語法真是簡單又好用 (不過…目前工作用的系統都還在用 2008R2 咧…新語法雖然好用，但現階段用不到呀)。

SELECT
    C_TYPE AS CITY_TYPE
   ,STRING_AGG(C_ID, ',') AS CITY_ID
   ,STRING_AGG(C_NAME, ',') AS CITY_NAME
FROM CITY po WITH (NOLOCK)
GROUP BY C_TYPE

參考：[小菜一碟] SQL Server FOR XML 退休，欄位資料合併讓 STRING_AGG 來。

Oracle 11gR2 跟 SQL Server 版本的寫法，目前為止只有私底下寫而已，專案上還沒用到。至於 Oracle 9.2i 的版本對於資料量少的資料表來說，真的是很方便。但若是資料量達到上萬筆，效能真的差到不行，最後還是放棄這種寫法。要這麼寫的話，請先衡量資料量再用吧。