~楓花雪岳~: [Challenge] 判斷有效的 GUID 並計算總和

星期三, 4月 03, 2013

[Challenge] 判斷有效的 GUID 並計算總和

Beyond Relational TSQL Beginners Challenge 13

The challenge involves validating the GUID values and perform horizontal and vertical count of the characters "1, 2, 3, 4, 5, A, B, C, D, E".

The definition of valid GUID is "The GUID value should contain all the characters 1, 2, 3, 4, 5, A, B, C, D, E".

e.g. In "7FC61235-F252-4E32-BD31-99FB1FC8A574" all the characters given in character set are present while in "7FX61235-X252-4Y32-BZ31-99FB1FC8Z574" some of them are missing even though they both are GUIDs. So the program should accept "7FC61235-F252-4E32-BD31-99FB1FC8A574" as a valid GUID.

A typical GUID looks like 704A9279-B245-4D4E-9616-7ACB6E9DF7A0.

Sample Data

Guid
------------------------------------
7FC61235-F252-4E32-BD31-99FB1FC8A574
D031BC00-ADD3-4C40-BE24-A613586FD42C
81B10B95-5A22-4D3A-949A-FF268103A554
ABCDE12345E

Expected Results

ValidGuid                            1 2 3 4 5 A B C D E Sum
------------------------------------ - - - - - - - - - - -----
7FC61235-F252-4E32-BD31-99FB1FC8A574 3 4 3 2 3 1 2 2 1 1  22
D031BC00-ADD3-4C40-BE24-A613586FD42C 2 2 3 4 1 2 2 3 4 1  24
Vertical Sum                         5 6 6 6 4 3 4 5 5 2  46

Rules

Output should be sorted in Ascending Order of ValidGuid.
Consider the character count for only the characters 1,2,3,4,5,A,B,C,D,E.
The output should be formatted as shown in 'Expected Result'.Column name should be exactly the same and the result must be sorted in Ascending order of ValidGuid.

個人解法

DECLARE @t TABLE(Guid VARCHAR(50))
INSERT INTO @t
SELECT '7FC61235-F252-4E32-BD31-99FB1FC8A574' UNION ALL
SELECT 'D031BC00-ADD3-4C40-BE24-A613586FD42C' UNION ALL
SELECT '81B10B95-5A22-4D3A-949A-FF268103A554' UNION ALL
SELECT 'ABCDE12345E'

;
WITH CTE AS -- 跑遞迴並拆解字串
(
  SELECT [Guid] , 1 AS Location , SUBSTRING([Guid],1,1) AS String
  FROM @t
  UNION ALL
  SELECT [Guid] , Location + 1 , SUBSTRING([Guid],Location + 1,1)
  FROM CTE
  WHERE Location < LEN([Guid])
)
SELECT 
  ISNULL([GUID],'Vertical Sum') AS ValidGuid , 
  SUM([1]) AS [1] , 
  SUM([2]) AS [2] ,
  SUM([3]) AS [3] ,
  SUM([4]) AS [4] ,
  SUM([5]) AS [5] ,
  SUM([A]) AS [A] ,
  SUM([B]) AS [B] ,
  SUM([C]) AS [C] ,
  SUM([D]) AS [D] ,
  SUM([E]) AS [E] ,
  SUM([SUM]) AS [SUM]  
FROM
  (
    SELECT [GUID] , [1],[2],[3],[4],[5],[A],[B],[C],[D],[E] ,
           [1] + [2] + [3] + [4] + [5] + [A] + [B] + [C] + [D] + [E] AS [SUM]
    FROM
      (
        SELECT S.[GUID] , S.String
        FROM CTE AS S 
          JOIN 
            (
              SELECT [Guid] , COUNT(String) AS Counts
              FROM CTE
              WHERE String LIKE '[0-9A-F]' -- 0-9 或 A-F 的數字
              GROUP BY [Guid]
              HAVING COUNT(String) = 32 -- 不包含 '-' 符號，GUID 長度為 32
            )  AS C ON S.GUID = C.GUID
      ) AS P
    PIVOT
      (
        COUNT(String) FOR String IN ([1],[2],[3],[4],[5],[A],[B],[C],[D],[E])
      ) AS PV
    WHERE 
      -- 根據題目給的條件
      [1] > 0 AND [2] > 0 AND [3] > 0 AND [4] > 0 AND [5] > 0 AND 
      [A] > 0 AND [B] > 0 AND [C] > 0 AND [D] > 0 AND [E] > 0
  ) AS T
GROUP BY [GUID] WITH ROLLUP

有效 GUID 判斷

從 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 格式的字串常數轉換，其中每一個 x 是範圍 0-9 或 a-f 的十六進位數字，且 uniqueidentifier 類型限制為 36 個字元（包含 '-' 符號）；因此利用 CTE 把字串拆後開，會根據上面敘述去判斷是否為 GUID。

SELECT [Guid] , COUNT(String) AS Counts
FROM CTE
WHERE String LIKE '[0-9A-F]' -- 判斷每個字元為 0-9 或 A-F 的數字
GROUP BY [Guid]
HAVING COUNT(String) = 32 -- 不包含 '-' 符號，GUID 長度為 32

~楓花雪岳~

星期三, 4月 03, 2013

[Challenge] 判斷有效的 GUID 並計算總和

沒有留言:

張貼留言