20xxfall-資料下載頁
2025-09-21 11:46本頁面
【導讀】1986成為ANSI標準,1987成為ISO標準。各家廠商的實作可能略有不同
【正文】 urned from SQL query with aggregate function (via GROUP BY) 若配合 GROUP BY將傳回多筆 SELECT CUSTOMER_STATE, COUNT(CUSTOMER_STATE) FROM CUSTOMER_V GROUP BY CUSTOMER_STATE。 Note: you can use singlevalue fields with aggregate functions if they are included in the GROUP BY clause Chapter 7 37 原始表格 SELECT area, count(*) FROM member GROUP BY area。 SELECT gender, count(*) FROM member GROUP BY gender。 Chapter 7 38 原始表格 SELECT gender, education, count(*) AS ppl FROM member GROUP BY gender, education。 Chapter 7 39 原始表格 SELECT gender, education, count(*) AS ppl FROM member GROUP BY gender, education ORDER BY count(*) DESC。 Chapter 7 40 原始表格 SELECT gender, education, count(*) AS ppl, max(age) FROM member GROUP BY gender, education。 使用不同的函數 Chapter 7 41 SELECT Example (7) Qualifying Results by Categories Using the HAVING Clause ? For use with GROUP BY ? 將 GROUP BY後的結果再用條件過濾的意思 ? 語法與 WHERE一樣 SELECT CUSTOMER_STATE, COUNT(CUSTOMER_STATE) FROM CUSTOMER_V GROUP BY CUSTOMER_STATE HAVING COUNT(CUSTOMER_STATE) 1。 Like a WHERE clause, but it operates on groups (categories), not on individual rows. Here, only those groups with total numbers greater than 1 will be included in final result Chapter 7 42 SELECT gender, education, count(*) AS ppl FROM member GROUP BY gender, education HAVING education=39。大學 39。 SELECT gender, education, count(*) AS ppl FROM member GROUP BY gender, education。 HAVING可以想成是 GROUP BY後的 WHERE Chapter 7 43 Using and Defining Views ? Views provide users controlled access to tables Ex. 只可看到某些欄位 , 或建立某些常用查詢 ? Dynamic View ? A “virtual table” created dynamically ? No data actually stored ? Based on SQL SELECT statement on base tables or other views ? Materialized View ? Copy or replication of data ? Data actually stored ? Must be refreshed periodically to match the corresponding base tables 需資料更新以維持一致性 , 少用 Chapter 7 44 Sample CREATE VIEW CREATE VIEW EXPENSIVE_STUFF_V AS SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE FROM PRODUCT_T WHERE UNIT_PRICE 300 。 ? View has a name ? View is based on a SELECT statement, and acts like Table ? 可分為 readonly view 或 updateable view (多為前者 ) Chapter 7 45 Advantages of Views ? Simplify query mands ? Provide customized view for user 常用查詢可建立為 view 善用 view可簡化複雜查詢 Disadvantages of Views ? Use processing time each time view is referenced ? May or may not be directly updateable 處理速度可能稍慢 有些 RDBMS不支援 updateable view
教學課件相關推薦
鄂ICP備17016276號-1