总计资料.pptxVIP

Chapter 5 ;-- 程式 5-1: 每年每位客戶所下的訂單總數 SELECT CustomerID, YEAR(OrderDate) AS Order_Year, COUNT(*) AS Order_Count FROM Orders WHERE CustomerID LIKE A% GROUP BY CustomerID, YEAR(OrderDate);;-- 程式 5-2: 每年由某位顧客產生、某位職員處理的訂單總數 SELECT CustomerID, EmployeeID, YEAR(OrderDate) AS Order_Year, COUNT(*) AS Order_Count FROM Orders WHERE CustomerID LIKE A% GROUP BY CustomerID, EmployeeID, YEAR(OrderDate);;-- 程式 5-3: 基本的 CUBE 查詢 SELECT CustomerID, YEAR(OrderDate) AS Order_Year, COUNT(*) AS Order_Count FROM Orders WHERE CustomerID LIKE A% GROUP BY CustomerID, YEAR(OrderDate) WITH CUBE;;-- 程式 5-4: CUBE 查詢與 GROUPING() 函數 SELECT CustomerID, GROUPING(CustomerID) AS Grp_Cust, YEAR(OrderDate) AS Order_Year, GROUPING(YEAR(OrderDate)) AS Grp_Year, COUNT(*) AS Order_Count FROM Orders WHERE CustomerID LIKE A% GROUP BY CustomerID, YEAR(OrderDate) WITH CUBE ;;-- 程式 5-5: 建立 Vbase_cube 檢視表來隱藏 CUBE 查詢的複雜性 CREATE VIEW Vbase_cube AS SELECT CustomerID, GROUPING(CustomerID) AS Grp_Cust, YEAR(OrderDate) AS Order_Year, GROUPING(YEAR(OrderDate)) AS Grp_Year, COUNT(*) as Order_Count FROM Orders WHERE CustomerID LIKE A% GROUP BY CustomerID, YEAR(OrderDate) WITH CUBE GO;-- 程式 5-6: 從 Vbase_cube 檢視表中選取所有資料列 SELECT * FROM Vbase_cube;-- 程式 5-7: 以你想要的值取代 NULL ISNULL(CustomerID, your_own_value) -- 程式 5-8: 加入你設定的值 your_own_value := CASE Grp_Cust WHEN 1 THEN ALL ELSE UNKNOWN END -- 程式 5-9: 以你設定的值取代 NULL ISNULL(CustomerID, CASE Grp_Cust WHEN 1 THEN ALL ELSE UNKNOWN END;SELECT * , ISNULL(CustomerID, CASE Grp_Cust WHEN 1 THEN ALL ELSE UNKNOWN END) FROM Vbase_cube;CustomerID的型態是nchar(5) -- 程式 5-10: UNKNOWN 被刪減成 UNKNO ISNULL(NULL /* which is char(5) */, UNKNOWN) = UNKNO CAST--明確轉換某種資料型別的運算式為另一種資料型別。 -- 程式 5-11: 避免 CustomerID 資料行裡的 UNKNOWN 被截短 ISNULL(CAST(CustomerID AS varchar(7)), CASE Grp_Cust WHEN 1 THEN ALL ELSE UNKNOWN END) Order_Year的型態原本是datetime -- 程式