星期五, 4月 13, 2012

[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


沒有留言:

張貼留言