[Challenge] 累計加總（Runing Total）

Beyond Relational 的 TSQL Challenge 65

• 資料來源（題目內有資料來源的 script）

Date       CustomerID Type  Amount
---------- ---------- ----- --------
2011-01-01 CUST1001   INV   12000.00
2011-01-02 CUST1001   PAY    3000.00
2011-01-03 CUST1001   INV    8000.00
2011-01-04 CUST1001   PAY    9000.00
2011-01-04 CUST1002   INV    1000.00
2011-01-05 CUST1002   PAY    5000.00
2011-01-05 CUST1002   INV    6000.00

• 結果

Date       CustomerID Type  Amount   Balance
---------- ---------- ----- -------- ---------
2011-01-01 CUST1001   INV   12000.00  12000.00
2011-01-02 CUST1001   PAY    3000.00   9000.00
2011-01-03 CUST1001   INV    8000.00  17000.00
2011-01-04 CUST1001   PAY    9000.00   8000.00
2011-01-04 CUST1002   INV    1000.00   1000.00
2011-01-05 CUST1002   INV    6000.00   7000.00
2011-01-05 CUST1002   PAY    5000.00   2000.00

• 規則：
1. 同一個客戶同一天會有多筆 INV 和 PAY 資料
2. 同一個客戶同一天內的全部的 INV 資料必須先計算，之後再計算 PAY 資料
3. 結果必須根據 CustomerID , Date , Type , Amount 來排序
4. Balance 可以為負

[SQL] 字串欄位轉置

利用尋找各班級前三名，來解釋字串欄位要如何轉置，在此不討論前三名是否有同分的情況。

-- 建立測試資料
DECLARE @Temp Table (class char(10),SdtName char(10),Score smallint)
INSERT INTO @Temp VALUES ('小叮噹班','大雄',10)
INSERT INTO @Temp VALUES ('小叮噹班','阿福',90)
INSERT INTO @Temp VALUES ('小叮噹班','技安',70)
INSERT INTO @Temp VALUES ('小叮噹班','宜靜',100)
INSERT INTO @Temp VALUES ('海賊王班','魯夫',20)
INSERT INTO @Temp VALUES ('海賊王班','娜美',90)
INSERT INTO @Temp VALUES ('海賊王班','騙人布',65)
INSERT INTO @Temp VALUES ('海賊王班','香吉士',30)
INSERT INTO @Temp VALUES ('海賊王班','索隆',20)
INSERT INTO @Temp VALUES ('網球王子班','不二',95)
INSERT INTO @Temp VALUES ('網球王子班','英二',75)
INSERT INTO @Temp VALUES ('網球王子班','手塚',99)
INSERT INTO @Temp VALUES ('網球王子班','越前',80)
INSERT INTO @Temp VALUES ('網球王子班','河村',20)

• CASE 搭配 MAX()

轉置的第一個要點是必須要有一個基準欄位來辨別前三名，在此利用 ROW_NUMBER() 來產生此基準欄位。

SELECT * ,
       ROW_NUMBER() OVER (PARTITION BY class ORDER BY Score DESC) AS ROWNO
FROM @Temp

[SQL] 群組字串連結

設計資料庫，常常會有一對多邏輯介面（檔頭、檔身設計），這樣的設計在產生報表時，為方便使用者閱讀，都會希望把檔身的多筆資料，整合在一行內(多筆字串資料合併成一行字串)。

公司在月底時，人事通常會提供一份報表 - 下個月員工排休表，給課長確認是否有哪一天太多人排休，會導致產線人力不足，就利用這份排休表來說明這個主題。

-- 建立測試資料
IF OBJECT_ID (N'Leave',N'U') IS NOT NULL
    DROP TABLE Leave
 
CREATE TABLE Leave (Date datetime,EmpName varchar(8))
INSERT INTO Leave VALUES('2011-08-01','張三')
INSERT INTO Leave VALUES('2011-08-02','張三')
INSERT INTO Leave VALUES('2011-08-22','張三')
INSERT INTO Leave VALUES('2011-08-01','李四')
INSERT INTO Leave VALUES('2011-08-22','李四')
INSERT INTO Leave VALUES('2011-08-01','王五')
INSERT INTO Leave VALUES('2011-08-23','王五')
INSERT INTO Leave VALUES('2011-08-24','王五')
INSERT INTO Leave VALUES('2011-08-25','王五')

使用者自訂函數

IF OBJECT_ID(N'dbo.getCommaString', N'FN') IS NOT NULL
    DROP FUNCTION dbo.getCommaString

-- 建立 FUNCTION
CREATE FUNCTION getCommaString(@date datetime) 
RETURNS varchar(500)  --500若不夠，請加大 
BEGIN
    DECLARE @string varchar(500)  --500若不夠，請加大 
    SET @string = ''
 
    SELECT @string = @string + EmpName + ','
    FROM Leave
    WHERE Date = @date
    ORDER BY EmpName
 
    SET @string = LEFT(@string,LEN(@string)-1) -- 把最後面的逗號後刪除
    RETURN @string
END

-- 使用自定函數來進行字串連結 
SELECT 
     CONVERT(char(10),Date,120) AS [日期],
     DATENAME(dw,Date) AS [星期],
     dbo.getCommaString(Date) AS [請假人員]
FROM Leave
GROUP BY Date

Cursor

-- 宣告一個 Table 變數來承接 CURSOR 跑完的結果
DECLARE @Temp table (Date datetime,String varchar(100))
 
DECLARE curTemp CURSOR
  FOR
      (
        SELECT Date
        FROM Leave
        GROUP BY Date
      )
 
OPEN curTemp
 
DECLARE @date as datetime
DECLARE @string as varchar(100)

FETCH NEXT FROM curTemp INTO @date 
  WHILE (@@FETCH_STATUS = 0) 
    BEGIN  

        SET @string = ''

        SELECT @string = @string + EmpName + ','
        FROM Leave
        WHERE Date = @date

        SET @string = LEFT(@string,LEN(@string)-1)

        INSERT INTO @Temp (Date,String)
          VALUES(@date,@string)
 
        FETCH NEXT FROM curTemp INTO @date
    END
 
CLOSE curTemp 
DEALLOCATE curTemp 

SELECT 
    CONVERT(char(10),Date,120) AS [日期],
    DATENAME(dw,Date) AS [星期],
    String AS [請假人員]
FROM @Temp

CTE

;
WITH CTE AS
(
  SELECT 
    Date , 
    CAST('' AS nvarchar(100)) AS Data ,
    CAST('' AS nvarchar(100)) AS String ,
    0 AS Counts ,
    COUNT(*) AS Total -- 資料總筆數
  FROM Leave 
  GROUP BY Date
  UNION ALL
  SELECT 
    L.Date , 
    CAST(L.EmpName AS NVARCHAR(100)) , 
    CAST(String + ',' + L.EmpName AS nvarchar(100)) , 
    Counts + 1 , 
    Total
  FROM CTE AS T
    JOIN Leave AS L ON T.Date = L.Date
      AND T.Data < L.EmpName -- 遞迴停止重要關鍵
)
SELECT 
  CONVERT(char(10),Date,120) AS [日期],
  DATENAME(dw,Date) AS [星期],
  STUFF(String,1,1,'') AS [請假人員]
FROM CTE
WHERE Counts = Total -- 利用資料總筆來判斷哪一筆是最後一筆
ORDER BY Date

FOR XML PATH 應用

SELECT 
  Date AS 日期,
  DATENAME(dw,Date) AS 星期 ,  
  (
    STUFF -- 說明 3
      (
        (
          SELECT ',' + EmpName -- 說明 2
          FROM LEave T2
          WHERE T2.Date = T1.Date
          FOR XML PATH('') -- 說明 1
        )
        , 1, 1, '' 
      )
  ) AS [請假人員]
FROM Leave T1
GROUP BY Date

• 說明
1. 依預設，PATH 模式會針對結果集的每個資料列產生 <row> 元素包裝函數。 您可以選擇性地指定元素名稱。 如果您有選擇，則會將指定名稱做為包裝函數的元素名稱。 如果您提供空白字串 (FOR XML PATH (''))，就不會產生包裝函數元素。
2. 不要給欄位名稱，任何沒有名稱的資料行都將予以內嵌。
3. 利用 STUFF() 來取代第一個逗號

STRING_AGG()

SQL Server 2017 推出的新功能

SELECT
  [Date] AS 日期,
  DATENAME(dw,Date) AS 星期 ,  
  STRING_AGG(EmpName , ',') AS [請假人員]
FROM Leave
GROUP BY [Date]

這五種作法中，CURSOR 和 CTE 比較不會應用在實務上，個人練習用。

• 參考資料
• MSND STUFF、FOR XML 子句的基本語法、沒有名稱的資料行
• 論壇問題回答 FOR XML PATH 應用
• 灵活运用 SQL SERVER FOR XML PATH
• CTE應用－將多筆查詢結果合併成逗號分隔字串
  
• 欄位合併效能比較：CTE vs FOR XML
  
• T-SQL STUFF – Taking it to the limit

[SQL] 判斷第一碼為數字或是字母

VFP 中可以利用 ISDIGIT() 來判斷字串最左邊字元是否為數字，ISALPHA() 來判斷字串最左邊字元是否為英文字母，SQL Server內可以透過 LIKE 來達成。

CREATE FUNCTION [dbo].[ISDIGIT]
(
   @string as nvarchar(250)
)
RETURNS bit
AS
  BEGIN
    DECLARE @true bit
  
    IF @string LIKE '[0-9]%'
       SET @true = 1
    ELSE
       SET @true = 0
 
    RETURN @true
  END

CREATE FUNCTION [dbo].[ISALPHA]
(
   @string as nvarchar(250)
)
RETURNS bit
AS
  BEGIN
    DECLARE @true bit
 
    IF @string LIKE '[A-Z]%' -- 不區分大小寫
       SET @true = 1
    ELSE
       SET @true = 0
  
    RETURN @true
  END

SELECT Val,
    '數字判斷' = CASE WHEN dbo.ISDIGIT(Val) = 1 THEN '數字' ELSE '' END,
    '字母判斷' = CASE WHEN dbo.ISALPHA(Val) = 1 THEN '字母' ELSE '' END
FROM
     (
       SELECT '123' AS Val
       UNION ALL
       SELECT '456'
       UNION ALL
       SELECT 'ABC'
       UNION ALL
       SELECT 'def' -- 故意小寫
     ) AS T

• 結果

參考資料：
• LIKE in MSDN