K8S kubeadm 使用 local build image (不撈取遠端鏡像儲存庫)

合併多筆資料欄位 為單一字串 (FOR XML PATH & STUFF)

MSDN 官方文件 - FOR XML PATH
MSDN 官方文件 - STUFF

DECLARE @T1 TABLE(Column1 VARCHAR(10))

INSERT INTO @T1
SELECT '1' UNION
SELECT '2' UNION
SELECT '3' 

-- STUFF ( str , start , length , replace_str )
-- STUFF ( 原字串, 起始位置, 移除長度, 替換字串)

-- 替換字串第一個字元
-- STUFF ( ',1,2,3', 1, 1, '')

SELECT STUFF (
    (
        SELECT ',' + Column1
        FROM  @T1
        FOR XML PATH('')
    ), 1,1,'')

CTE 範例

MSDN 官方文件 - CTE

DECLARE @T1 TABLE(T1Column1 VARCHAR(10))
DECLARE @T2 TABLE(T2Column1 VARCHAR(10))
DECLARE @T3 TABLE(T3Column1 VARCHAR(10))

INSERT INTO @T1 SELECT 'T1-1' UNION SELECT 'T1-2'
INSERT INTO @T2 SELECT 'T2-1' UNION SELECT 'T2-2'
INSERT INTO @T3 SELECT 'T3-1' UNION SELECT 'T3-2'


;WITH QueryT1 AS
(
    SELECT * FROM @T1
),
QueryT2 AS
(
    SELECT * FROM @T2
),
QueryT3 AS
(
    SELECT * FROM @T3
)
SELECT * FROM QueryT1 t1
INNER JOIN QueryT2 t2 ON 1 = 1
INNER JOIN QueryT3 t3 ON 1 = 1

分頁處理範例

-- Init Data
DECLARE @T1 TABLE(ID INT, T1Column1 VARCHAR(10))
DECLARE @Index INT = 0, @TempData VARCHAR(6);

-- Insert Data
WHILE @Index < 250
BEGIN
    INSERT INTO @T1 VALUES (@Index, 'Data-' + CONVERT(VARCHAR(6), @Index));
    SET @Index = @Index + 1;
END


DECLARE @PageIndex        INT = 1        -- 目前頁次
DECLARE @PageSize        INT = 50    -- 每頁筆數
DECLARE @TotalRecords    INT = 0        -- 總筆數

SELECT *
INTO #ResultData
FROM ( 
        -- 業務邏輯 BEGIN
        SELECT 
            ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNo,
            T1Column1
        FROM @T1
        -- 業務邏輯 END
     ) DataList

-- 分頁處理
SELECT * 
FROM #ResultData
WHERE RowNo BETWEEN @PageSize * ( @PageIndex - 1 ) + 1 AND ( @PageSize * @PageIndex )
ORDER BY RowNo

-- 取總筆數
SELECT @TotalRecords = Count(1) FROM #ResultData
SELECT @TotalRecords

GOTO 範例

MSDN 官方文件 - GOTO

DECLARE @TempNumber INT = Round(RAND() * 100, 0);
DECLARE @Level INT = 0;
PRINT('START')

IF ( @TempNumber < 20)
BEGIN
    GOTO End_Proc;    
END

SET @Level += 1;
PRINT('第一關檢核:數字 > 20 通過')


IF ( @TempNumber < 50)
BEGIN
    GOTO End_Proc;    
END

SET @Level += 1;
PRINT('第二關檢核:數字 > 50 通過')

IF ( @TempNumber < 80)
BEGIN
    GOTO End_Proc;    
END

SET @Level += 1;
PRINT('第三關檢核:數字 > 80 通過')

End_Proc:
    PRINT('數字' + CONVERT(VARCHAR(6), @TempNumber) + ' 共通過' + CONVERT(VARCHAR(6), @Level) + '關檢核')
    PRINT('END')

OUTPUT範例

MSDN 官方文件 - OUTPUT

-- 可接在 INSERT、UPDATE、DELETE、MERGE 後使用

-- 寫入 @T1 的資料 OUTPUT 也寫入 @TempData

DECLARE @T1 TABLE(Column1 VARCHAR(10))
DECLARE @TempData TABLE(TempColumn1 VARCHAR(10))

INSERT INTO @T1
OUTPUT INSERTED.Column1
INTO @TempData (TempColumn1)
SELECT '1' UNION
SELECT '2' UNION
SELECT '3' 

SELECT TempColumn1 AS '新增過的資料' FROM @TempData
DELETE @TempData

-- 更新 @T1 的資料 OUTPUT 寫入 @TempData
UPDATE @T1
SET Column1 = '1'
OUTPUT INSERTED.Column1
INTO @TempData (TempColumn1)

SELECT TempColumn1 AS '更新過的資料' FROM @TempData
DELETE @TempData

-- 刪除 @T1 的資料 OUTPUT 寫入 @TempData
DELETE @T1
OUTPUT DELETED.Column1
INTO @TempData (TempColumn1)
WHERE Column1 = '1'


SELECT TempColumn1 AS '刪除過的資料' FROM @TempData

-- MERGE @T1 的資料 OUTPUT 寫入 @MyTempTable
INSERT INTO @T1
SELECT '1' UNION ALL
SELECT '1' UNION ALL
SELECT '3' 

DECLARE @MyTempTable TABLE
    (ExistingData VARCHAR(10),
     ActionTaken nvarchar(10),  
     NewData VARCHAR(10)
    )

MERGE @T1 t1
USING ( SELECT '1' 'DataColumn') data
ON t1.Column1 = data.DataColumn
WHEN MATCHED THEN
    UPDATE SET t1.Column1 = '100'
WHEN NOT MATCHED THEN
    INSERT VALUES ('999')
OUTPUT deleted.Column1, $action, inserted.Column1 INTO @MyTempTable; 

SELECT ExistingData AS '舊資料', ActionTaken AS '操作行為', NewData AS '新資料' FROM @MyTempTable

LAG、LEAD 範例

MSDN 官方文件 - LAG
MSDN 官方文件 - LEAD

--@table 原始資料
DECLARE @table TABLE 
(
    [date] datetime,
    [user] nvarchar(10),
    [qty] int
)

INSERT INTO @table ([date],[user],[qty])
VALUES
('2021/10/1', 'Allen', 10),
('2022/1/25', 'Allen', 20),
('2022/3/10', 'Allen', 30),
('2022/5/20', 'Allen', 20),
('2022/6/7', 'Allen', 20),

('2021/6/1', 'Ben', 40),     
('2022/4/1', 'Ben', 20),
('2022/4/10', 'Ben', 10),
('2022/5/3', 'Ben', 10),
('2022/6/7', 'Ben', 40);

SELECT t.*,
        DATEDIFF(DAY,
                LAG([date]) OVER (PARTITION BY [user] ORDER BY [date]),
                [date]
                ) AS '與前一筆資料差距天數'
FROM  @table t 

SELECT t.*,
        ABS(DATEDIFF(DAY,
                LEAD([date]) OVER (PARTITION BY [user] ORDER BY [date]),
                [date]
                )) AS '與後一筆資料差距天數'
FROM  @table t 

---

轉載請註明來源，若有任何錯誤或表達不清楚的地方，歡迎在下方評論區留言，也可以來信至 leozheng0621@gmail.com
如果文章對您有幫助，歡迎斗內(donate)，請我喝杯咖啡