Now continue ADVANCED SELECT FROM Select SELECT selectlist

  • Slides: 37
Download presentation

Now continue…. ADVANCED “SELECT” & “FROM”

Now continue…. ADVANCED “SELECT” & “FROM”

Select 基本結構 SELECT select_list [INTO new_table] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [HAVING

Select 基本結構 SELECT select_list [INTO new_table] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ] [COMPUTE expression] 務必照順序排列

SELECT ~ For what? • SELECT * : 資料表中的所有欄位 • SELECT column_name : 指定欄位

SELECT ~ For what? • SELECT * : 資料表中的所有欄位 • SELECT column_name : 指定欄位 – EX: SELECT 編號, 書名 • SELECT expression : 用運算式代替欄位名稱 – EX : SELECT 定價*0. 8

GROUP BY (多欄位or 運算式) SELECT 客戶名稱 , DATEPART(YEAR, 日期) AS 年份 , SUM(數量) AS

GROUP BY (多欄位or 運算式) SELECT 客戶名稱 , DATEPART(YEAR, 日期) AS 年份 , SUM(數量) AS 出貨數量 FROM 出貨記錄 GROUP BY 客戶名稱, DATEPART(YEAR, 日期) ORDER BY 客戶名稱, DATEPART(YEAR, 日期)

GROUP BY 使用需注意… • EX: SELECT A, B FROM table 1 GROUP BY A

GROUP BY 使用需注意… • EX: SELECT A, B FROM table 1 GROUP BY A (ERROR!! 因為B沒有在GROUP BY 中)