[Challenge] 統計電話號碼

Beyond Relational TSQL Beginners Challenge 6

挑戰計算每個人的電話號碼數目並產生總和資料，主要目地是測試對字串的了解和 T-SQL 的總和技巧。

• 資料來源

PersonTable

PersonId PersonName
-------- ------------------
1      Deepak Kumar Goyal
2      Niladri Biswas

ContactDetail Table

PersonId MobileNumber
-------- ---------------------
1      9886551234,9445612356
2      9886334510

• 產生結果

PersonId PersonName         MobileNumber           RecordCount
-------- ------------------ ---------------------  -----------
1        Deepak Kumar Goyal 9886551234,9445612356  2
2        Niladri Biswas     9886334510             1
          Total Mobiles:                           3

• 規則
1. PersonId should be sorted in Ascending Order.
2. If a person does not have any mobile, then his/her Record Count will be 0.
3. If a person does not have any name (Blank or NULL assigned)or Mobile number, his/her record should not be counted.
4. If a person does not have any name (Blank or NULL assigned) but is having Mobile Number, his/her record should not be counted.

• 個人解法

利用 CTE 拆解字串並搭配GROUP BY WITH ROLLUP 使用來找出全部的電話號碼數量。

DECLARE @Person TABLE(PersonId INT,PersonName VARCHAR(20))
INSERT INTO @Person 
SELECT 1,'Deepak Kumar Goyal' UNION ALL
SELECT 2,'Niladri Biswas' 

DECLARE @ContactDetail TABLE(PersonId INT,MobileNumber VARCHAR(100))
INSERT INTO @ContactDetail
SELECT 1, '9886551234,9445612356' UNION ALL    
SELECT 2,'9886334510'     

;
WITH CTE AS
(
  SELECT Personid , MobileNumber , 1 AS start , CHARINDEX(',',MobileNumber,1) AS [End]
  FROM @ContactDetail
  UNION ALL
  SELECT Personid , MobileNumber , [End] + 1 , CHARINDEX(',',MobileNumber,[End] + 1) 
  FROM CTE
  WHERE [End] > 0
)
SELECT 
  P.Personid , 
  ISNULL(P.PersonName,'Total Mobiles:') AS PersonName , 
  C.MobileNumber , 
  ISNULL(T.RecordCount,0) AS RecordCount
FROM @Person AS P
  JOIN @ContactDetail AS C ON P.Personid = C.Personid
  FULL JOIN
    (
      SELECT Personid , COUNT(Personid) AS RecordCount
      FROM CTE
      GROUP BY Personid WITH ROLLUP
    ) AS T ON P.Personid = T.Personid

• 參考 Solutions 後修改的 T-SQL 語法

資料來源是用逗號來分隔電話號碼，因此利用 REPLACE() 取代逗號，再使用 LEN() 來計算取代前後長度來判斷電話號碼數量

DECLARE @Person TABLE(PersonId INT,PersonName VARCHAR(20))
INSERT INTO @Person 
SELECT 1,'Deepak Kumar Goyal' UNION ALL
SELECT 2,'Niladri Biswas'
 
DECLARE @ContactDetail TABLE(PersonId INT,MobileNumber VARCHAR(100))
INSERT INTO @ContactDetail
SELECT 1, '9886551234,9445612356' UNION ALL   
SELECT 2,'9886334510'    

SELECT 
  P.Personid , 
  ISNULL(P.PersonName,'Total Mobiles:') AS PersonName , 
  D.MobileNumber , 
  ISNULL(C.RecordCount,0) AS RecordCount
FROM @Person AS P
  JOIN @ContactDetail AS D ON P.Personid = D.Personid
  FULL JOIN 
    (
      SELECT 
        Personid , 
        SUM(((LEN(MobileNumber) - LEN(REPLACE(MobileNumber,',',''))) / LEN(',')) + 1) AS RecordCount
      FROM @ContactDetail
      WHERE LEN(MobileNumber) > 0
      GROUP BY Personid WITH ROLLUP
    ) AS C ON P.Personid = C.Personid

參考資料
• TSQL Beginners Challenge 6 - Count the number of mobile phones and generate a summary
• MSDN 使用 ROLLUP 來摘要資料、搭配 ROLLUP、CUBE 和 GROUPING SETS 使用 GROUP BY、GROUPING SETS、ROLLUP 和 CUBE 的執行計畫、GROUP BY、GROUPING SETS 對等項目、GROUPING()