第 10 章 更多的查詢技巧.

Presentation on theme: "第 10 章 更多的查詢技巧."— Presentation transcript:

1 第 10 章 更多的查詢技巧

2 本章重點 10-1 用UNION 合併多個查詢結果 10-2 子查詢 Subquery
10-3 使用 SQL Server Management Studio 設計 SQL 查詢 10-4 T-SQL 的常數 10-5 隱含式型別轉換 10-6 T-SQL 的運算子 10-7 運算子的優先順序 10-8 處理欄位中的 NULL 值

3 本章重點 10-12 彙總函數搭配 OVER 子句的應用 10-9 使用 APPLY 運算子在查詢時 同時呼叫函數進行運算
10-10 邏輯函數：I I F ( )、CHOOSE() 10-11 排序函數：ROW_NUMBER()、 RANK() 與 DENSE_RANK() 10-12 彙總函數搭配 OVER 子句的應用 10-13 使用 PIVOT 與 UNPIVOT 將 直式資料表轉為橫式

4 10-1 用 UNION 合併多個查詢結果

5 用 UNION 合併多個查詢結果 UNION 的條件與結果 UNION 的語法 應用範例

6 UNION 的條件與結果 欲合併的查詢結果, 其欄位數必須相同 欲合併的查詢結果, 其對應的欄位
合併結果的欄位名稱會以第一個查詢結果的欄位名稱為名, 其他查詢結果的欄位名稱則會被忽略掉。 合併時, 若對應的欄位具備不同的資料型別, 則SQL Server 會進行相容性的型別轉換, 轉換的原則是以"可容納較多資料的型別為主"。

7 UNION 的語法

8 UNION 的語法 select_statement ALL 小括弧( )
GROUP BY 和 HAVING 子句只能用在個別的select_statement 中, 不可用於整個UNION 敘述的 最後。 ORDER BY 及 COMPUTE 子句則只能用在整個敘 述的最後, 針對最後的合併結果做排序或計算, 不能 用在個別的 select_statement 中。 只有第一個 select_statement 可以設定 INTO 子句。 ALL 小括弧( )

9 應用範例

10 應用範例

11 應用範例 不刪除資料表合併後重複的資料

12 應用範例

13 應用範例 加入臨時的資料

14 10-2 子查詢 Subquery 子查詢的語法與範例 子查詢的類型與處理方式 獨立子查詢與關聯子查詢

15 子查詢的語法與範例 整個子查詢敘述需用小括弧 ( ) 括住。 子查詢中不能使用 INTO 子句。
若子查詢中有用到 "SELECT TOP n...", 才可設定ORDER BY 子句來排序。

16 子查詢的類型與處理方式 方法 1 ：直接取值－直接使用子查詢的傳回值, 例如用 =、>、< 做比較, 或進行加減乘除等運算。
方法 2： 比對清單－使用 IN、ALL、或 ANY (SOME) 運算子判斷某個值是否存在於傳回清單中, 其比對結果為 True 或 False。 方法 3 ：測試存在－使用 EXISTS 運算子判斷是否有傳回資料, 其測試結果亦為 True 或 False。

17 子查詢的類型與處理方式

18 子查詢的類型與處理方式 直接取值的子查詢

19 子查詢的類型與處理方式 比對清單的子查詢 IN

20 子查詢的類型與處理方式 ALL

21 子查詢的類型與處理方式 ANY、SOME

22 子查詢的類型與處理方式 測試存在的子查詢

23 相同功能的不同查詢方式

24 獨立子查詢與關聯子查詢 獨立子查詢 是指可以脫離主查詢,單獨執行的子查詢。

25 獨立子查詢與關聯子查詢 關聯子查詢 是指無法單獨存在的子查詢。

26 10-3 使用 SQL Server Management Studio 設計 SQL 查詢
查詢設計工具 設計查詢的技巧─ 各窗格的操作 設定群組欄位 整個查詢敘述的屬性設定

27 查詢設計工具

28 查詢設計工具

29 查詢設計工具

30 設計查詢的技巧─ 各窗格的操作 圖表窗格 載入/移除資料來源

31 設計查詢的技巧─ 各窗格的操作 在列示窗中雙按您要加入的資料來源, 或選取資料來源再按加入鈕, 即可將資料來源載入圖表窗格中。

32 設計查詢的技巧─ 各窗格的操作

33 設定資料表別名

34 設計查詢的技巧─ 各窗格的操作 JOIN 資料表

35 設計查詢的技巧─ 各窗格的操作

36 設計查詢的技巧─ 各窗格的操作

37 設計查詢的技巧─ 各窗格的操作

38 設計查詢的技巧─ 各窗格的操作

39 設計查詢的技巧─ 各窗格的操作

40 設計查詢的技巧─ 各窗格的操作 設定顯示欄位及排序欄位

41 設計查詢的技巧─ 各窗格的操作 準則窗格

42 設計查詢的技巧─ 各窗格的操作

43 設計查詢的技巧─ 各窗格的操作

44 設計查詢的技巧─ 各窗格的操作 SQL 窗格

45 設計查詢的技巧─ 各窗格的操作 結果窗格 結果窗格顧名思義就是顯示查詢結果的地方。在設計好查詢敘述後, 按下執行 鈕, 查詢結果就會出現在此窗格中。

46 設定群組欄位

47 設定群組欄位

48 設定群組欄位

49 整個查詢敘述的屬性設定

50 在一般的查詢窗格中使用查詢設計工具

51 在一般的查詢窗格中使用查詢設計工具

52 在一般的查詢窗格中使用查詢設計工具

53 10-4 T-SQL 的常數 字串常數 (Character string constants)
必須以單引號括起來,若字串內容本身即含有單引號時, 可用連續兩個單引號來表示。

54 T-SQL 的常數 Unicode 字串常數 (Unicode string) 和字串常數表示法相同,但必須在字串最前面加上一個大寫的 N。

55 T-SQL 的常數 二元碼常數 (Binary constants) 必須以 0x 開頭的 16 進位數值來表示,前後不必加引號。

56 T-SQL 的常數 位元常數 (bit constants) 只有 0 與 1 兩種值。
日期時間常數(datetime constants) 必須用單引號括起來的日期或時間字串。

57 更改年月日的順序

58 更改年月日的順序

59 更改年月日的順序

60 更改年月日的順序

61 統一的日期表示法

62 統一的日期表示法

63 統一的日期表示法

64 T-SQL 的常數 整數常數 (Integer constants) 精確位數常數 (Decimal constants)
是指 numeric 或 decimal 型別的資料,以含有小數點的數值表示。 浮點常數 (Float and Real constants) 是指 float 或 real 型別的資料,以科學記號表示。 貨幣常數 (Money constants) 是指 money 或 smallmoney 型別的資料,以 $ 開頭的數值表示。 標記常數 (Uniqueidentifier constants) 是 uniqueidentifier 型別的資料,可以用字串或二元碼常數表示。

65 10-5 隱含式型別轉換 型別轉換的兩種類型 轉換型別的優先順序 型別轉換的限制

66 型別轉換的兩種類型 資料型別的轉換分成兩種類型, 像剛才 smallint 和int 的資料進行運算, SQL Server會自動將 smallint 資料轉換成 int 型別, 這樣的 “自動型別轉換” 就稱為隱含式型別轉換 (Implicit conversion)；而需由我們主動以 CAST 或 CONVERT 函數來轉換型別時, 則稱為強迫式型別轉換 (Explicit conversion) 或稱明確轉換。

67 轉換型別的優先順序

68 型別轉換的限制 當然, 並不是每種型別都可以相互轉換的, 例如nchar 型別是無法轉換成 image 的；而有些型別只能用強迫式型別轉換而不允許隱含式型別轉換, 例如 nchar 轉換成 binary。

69 10-6 T-SQL 的運算子 指定運算子 算數運算子 比較運算子 邏輯運算子 位元運算子 字串連結運算子 單一運算元運算子 複合運算子

70 指定運算子 將數值或字串等資料指定給欄位或變數。

71 算數運算子 括 + (加)、- (減)、* (乘)、/ (除) 與 % (整數相除的餘數)。

72 算數運算子

73 比較運算子 比較數字的大小,或是字串的差異。

74 比較運算子

75 邏輯運算子 AND、OR:是做為兩個陳述式的邏輯判斷之用。

76 邏輯運算子 BETWEEN:表示在兩者之間, 因此只要是在給定條件之間的資料都符合要求。

77 邏輯運算子 IN：用來判斷給定的值是否在指定的項目列表或是子查詢中。

78 邏輯運算子 LIKE:用指定的字串來找尋記錄。

79 邏輯運算子 NOT:可將邏輯運算元的值反向。

80 位元運算子 &:運算子前後的兩個運算元都為 1 的時候,結果為 1。只要有一個不是 1,則結果為 0。
|:運算子前後的兩個運算元只要有一個是 1,則結果就是 1,只有當兩個都是0 的時候才會是 0。 ^:為互斥運算子,當兩個運算元的值不一樣的時候才會是 1,否則為 0。

81 字串連結運算子

82 單一運算元運算子

83 複合運算子

84 10-7 運算子的優先順序

85 10-8 處理欄位中的 NULL 值 NULL 值的運算 ISNULL( ) 函數 檢查是否為 NULL 值

86 NULL 值的運算

87 ISNULL( ) 函數

88 檢查是否為 NULL 值

89 檢查是否為 NULL 值

90 10-9 使用 APPLY 運算子在查詢時 同時呼叫函數進行運算

91 使用 APPLY 運算子在查詢時 同時呼叫函數進行運算

92 使用 APPLY 運算子在查詢時 同時呼叫函數進行運算

93 使用 APPLY 運算子在查詢時 同時呼叫函數進行運算

94 10-10 邏輯函數：I I F ( )、CHOOSE()

95 邏輯函數：I I F ( )、CHOOSE()

96 邏輯函數：I I F ( )、CHOOSE()

97 邏輯函數：I I F ( )、CHOOSE()

98 10-11 排序函數：ROW_NUMBER()、 RANK() 與 DENSE_RANK()
針對資料表的欄位進行排序 指定查詢範圍 分組排名

99 針對資料表的欄位進行排序

100 針對資料表的欄位進行排序

101 針對資料表的欄位進行排序

102 指定查詢範圍

103 分組排名

104 10-12 彙總函數搭配 OVER 子句的應用 使用 OVER 子句做分組彙總 使用 OVER 子句計算移動平均值、累計總和

105 使用 OVER 子句做分組彙總

106 使用 OVER 子句做分組彙總

107 使用 OVER 子句做分組彙總

108 使用 OVER 子句做分組彙總

109 使用 OVER 子句計算移動平均值、 累計總和

110 使用 OVER 子句計算移動平均值、 累計總和

111 使用 OVER 子句計算移動平均值、 累計總和

112 使用 OVER 子句計算移動平均值、 累計總和

113 使用 OVER 子句計算移動平均值、 累計總和

114 10-13 使用 PIVOT 與 UNPIVOT 將直式資料表轉為橫式

115 使用 PIVOT 與 UNPIVOT 將直式資料表轉為橫式

116 使用 PIVOT 與 UNPIVOT 將直式資料表轉為橫式

117 使用 PIVOT 與 UNPIVOT 將直式資料表轉為橫式

118 PIVOT 的語法與使用方法

119 PIVOT 的語法與使用方法

120 PIVOT 的語法與使用方法

121 PIVOT 的語法與使用方法

122 UNPIVOT 的語法與使用方法

123 UNPIVOT 的語法與使用方法

124 UNPIVOT 的語法與使用方法