[Challenge] 計算字元在字串中出現次數

Beyond Relational TSQL Beginners Challenge 14

This challenge is all about counting the number of occurrence of characters in a string using a SET based query. The task is to list all the characters and count of that character within the given string.

• 資料來源

Data
----
12
xx

• 產生結果

Data Chars NumberOfOccurance
---- ----- -------------------
12  1    1 appears (1) times
12  2    2 appears (1) times
xx  x    x appears (2) times

• 規則
1. Results must be sorted in ascending order of Data and then by Character.
2. The output should be in the same way as it has been shown.

[SQL] 次序函數應用-重覆資料不顯示

此論壇問題是要重覆資料不顯示，一看見問題是直覺這不是應該在報表中設定，就可以達到的效果。

DECLARE @Temp TABLE (STDID char(4),StdName char(10),Course char(20))
INSERT INTO @Temp VALUES('0001','張三','ASP.NET')
INSERT INTO @Temp VALUES('0001','張三','HTML5')
INSERT INTO @Temp VALUES('0001','張三','CSS')
INSERT INTO @Temp VALUES('0002','李四','C#')
INSERT INTO @Temp VALUES('0003','王五','T-SQL')
INSERT INTO @Temp VALUES('0003','王五','SSRS')
INSERT INTO @Temp VALUES('0003','王五','SSIS')
INSERT INTO @Temp VALUES('0003','王五','Perfomance Tuning')

SELECT 
  IIF(ROWNO = 1 , STDID , NULL) AS STDID ,
  IIF(ROWNO = 1 , STDName , NULL) AS STDName ,
  Course
FROM
  (
    SELECT * ,
      ROW_NUMBER() OVER (PARTITION BY STDID ORDER BY STDID) AS ROWNO
    FROM @Temp
  ) AS T

• 邏輯說明

利用 ROW_NUMBER() 根據 STDID 去跑出排序，在 SELECT 中根據排序利用 CASE WHEN 或是 IIF 來判斷是不是第一筆資料，非第一筆資料則用 NULL 來表示。

延伸閱讀
• [SQL] 次序函數應用-資料分組
• [SQL] 次序函數應用-判斷是否為連續日期

參考資料
• 論壇問題出處
• 論壇討論

[Win] SMTP 設定

學習 SQL Server Database Mail 時，必須先設定 SMTP 才能使用 Database Mail 來發信，紀錄一下在 Windows Server 2008 上設定 SMTP 的過程。

• 開始 => 所有程式 => 系統管理工具 => 伺服器管理員 => 功能 => 新增功能

• 勾選 SMTP 伺服器，系統會同時詢問並自動勾選 SMTP 伺服器工具 和 IIS 6.0（未安裝的話）

• 安裝進行中

• 安裝完成

[SQL] JOIN ON 條件中使用 CASE 來判斷

此論壇問題是要利用 @TempA 的 A2 欄位 JOIN @TempB 的 B1 欄位，假如 A2 欄位資料為 AA+ JOIN 不到資料，就必須改為 AA- 去JOIN，直到找不到資料。

DECLARE @TempA TABLE(A1 char(4),A2 char(3))
DECLARE @TempB TABLE(B1 char(3),B2 char(10))
INSERT INTO @TempA VALUES('0001','AA+')
INSERT INTO @TempA VALUES('0002','BB+')
INSERT INTO @TempB VALUES('AA-','值1')
INSERT INTO @TempB VALUES('BB+','值2')
INSERT INTO @TempB VALUES('BB-','值3')

-- 會產生無法解釋的非預期結果
SELECT A.A1,A.A2,B.B1,B.B2 
FROM @TempA AS A
  LEFT JOIN @TempB AS B ON
    (
      CASE
        WHEN (A.A2 = B.B1) THEN A.A2 
        ELSE SUBSTRING(A.A2,1,2) + '-' END
    ) = B.B1

-- 建議用法
SELECT 
  A.A1 , 
  A.A2 , 
  ISNULL(B1.B1,B2.B1) AS B1, 
  ISNULL(B1.B2,B2.B2) AS B2
FROM @TempA AS A 
  LEFT JOIN @TempB AS B1 ON A.A2 = B1.B1
  LEFT JOIN @TempB AS B2 ON SUBSTRING(A.A2,1,2) + '-' = B2.B1

在 JOIN ON 使用 CASE WHEN 來判斷連結條件或是多個條件比較，會產生無法解釋的非預期結果，要盡量避免此用法；在此利用多個 LEFT JOIN 搭配 ISNULL() OR COALESCE() 來達到目的，確保結果產生。

MSDN ON 說明

ON 為聯結所根據的條件，指定聯結所根據的條件。條件可以指定任何述詞 (雖然通常都是使用資料行和比較運算子)

當條件指定資料行時，這些資料行不必有相同的名稱或相同的資料類型；不過，如果資料類型不同，它們必須相容的類型或 SQL Server 可以利用隱含方式轉換的類型。 如果資料類型無法利用隱含方式轉換，條件必須藉由 CONVERT 函數，利用明確方式轉換資料類型。

ON 子句中可以有僅涉及其中一個聯結資料表的述詞。 這類述詞也可以在查詢的 WHERE 子句中。 雖然這類述詞的放置不會影響 INNER 聯結，不過，如果涉及 OUTER 聯結，就可能會造成不同的結果。 這是因為 ON 子句中的述詞會套用至聯結之前的資料表，但在語意上，WHERE 子句則套用至聯結的結果。

參考資料
• 論壇問題出處
• Conditional Joins in SQL Server
• MSDN FROM

[Challenge] 電影時刻表

Beyond Relationa TSQL Beginners Challenge 8

這個問題有點奇怪，給了兩個 Table Data，但結果好像只要一個 Table Data 就可以產生。

• 資料來源

Table: MovieReleaseDates

Movie1     Movie2  Movie3  Movie4  Movie5
----------  ---------- ---------- ---------- ----------
2010-01-20  2010-02-15  2010-02-02  2010-03-31  2010-04-16 
2010-01-22  2010-02-16  2010-02-04  2010-04-05  2010-04-18 

Table: MovieReleasedIn

City   MovieNames ReleaseDate
--------- ---------- -----------
Bangalore Movie1  2010-01-22 
Bangalore Movie2  2010-02-15 
Bangalore Movie3  2010-02-04 
Bangalore Movie4  2010-04-05 
Bangalore Movie5  2010-04-16     

• 產生結果

City   Sun Mon     Tue Wed Thu    Fri     Sat  
--------- --- ------------ --- --- ------------- ------------- ---  
Bangalore NA  NA     NA  NA  NA    Movie1(01/22) NA 
Bangalore NA  Movie2(02/15)NA  NA  NA    NA         NA 
Bangalore NA  NA     NA  NA  Movie3(02/04) NA         NA 
Bangalore NA  Movie4(04/05)NA  NA  NA    NA         NA 
Bangalore NA  NA     NA  NA  NA    Movie5(04/16) NA  

• 個人解法

DECLARE @tblMovieReleasedIn TABLE
(
  City VARCHAR(20), 
  MovieNames VARCHAR(20),
  ReleaseDate Datetime
)
INSERT INTO @tblMovieReleasedIn
SELECT 'Bangalore','Movie1','01/22/2010' UNION ALL
SELECT 'Bangalore','Movie2','02/15/2010' UNION ALL
SELECT 'Bangalore','Movie3','02/04/2010' UNION ALL
SELECT 'Bangalore','Movie4','04/05/2010' UNION ALL
SELECT 'Bangalore','Movie5','04/16/2010' 

SELECT 
  City , 
  ISNULL([1],'NA') AS Sun , 
  ISNULL([2],'NA') AS Mon , 
  ISNULL([3],'NA') AS Tue , 
  ISNULL([4],'NA') AS Wed , 
  ISNULL([5],'NA') AS Thu , 
  ISNULL([6],'NA') AS Fri , 
  ISNULL([7],'NA') AS Sat
FROM
  (
    SELECT 
      City , 
      MovieNames , 
      MovieNames + '(' + LEFT(CONVERT(char(10),ReleaseDate,101),5) + ')' AS Data , 
      DATEPArt(dw,ReleaseDate) AS dw 
    FROM  @tblMovieReleasedIn
  ) AS P
PIVOT
  (
    MAX(Data) FOR dw IN ([1],[2],[3],[4],[5],[6],[7])
  ) AS PV
ORDER BY City

參考資料
• TSQL Beginners Challenge 8 - Identify the release dates of movies on different cities
• Solutions - 兩個 Table Data 都有使用到