本文實例講述了SQL Server簡單實現數據的日報和月報功能。分享給大家供大家參考,具體如下:
--320, SQL SERVER 日報 --查詢2009-01-01當天客戶A1,A2,A3的訂單數量 SELECT Cust_Name , CONVERT(CHAR(10), Order_Date, 120) Order_Date , SUM(Qty) Qty FROM Orders_Big WHERE 1=1 AND Cust_Name IN ('A1', 'A2', 'A3') AND Order_Date >= '2009-01-01' AND Order_Date<'2009-01-02' /*AND Order_Date -- BETWEEN...AND相當於>=和<= BETWEEN '2009-01-01' AND '2009-01-02'*/ GROUP BY Cust_Name , CONVERT(CHAR(10), Order_Date, 120) ORDER BY 2, 1 --321. ORACLE 日報 --查詢2009-01-01當天客戶A1,A2,A3的訂單數量 SELECT Cust_Name , TO_CHAR(Order_Date, 'YYYY-MM-DD') Order_Date --方法一:轉換成字符串類型 --, TRUNC(Order_Date) Order_Date --方法二:將時分秒信息截斷 , SUM(Qty) Qty FROM Orders_Big WHERE 1=1 AND Cust_Name IN ('A1', 'A2', 'A3') AND Order_Date >= DATE'2009-01-01' AND Order_Date<DATE'2009-01-02' GROUP BY Cust_Name , TO_CHAR(Order_Date, 'YYYY-MM-DD')--方法一 --, TRUNC(Order_Date)--方法二 ORDER BY 2, 1 ------------------------------10.2------------------------------------ --326,SQL SERVER 月報 SELECT Cust_Name , CONVERT(CHAR(7), Order_Date, 120) Order_YrMs , CAST(CONVERT(CHAR(7), Order_Date, 120) + '-01' AS DATETIME) Order_Date , SUM(Qty) Qty FROM Orders_Big WHERE 1=1 AND Cust_Name IN ('A1', 'A2', 'A3') AND Order_Date >= '2009-01-01' AND Order_Date<'2009-02-01' GROUP BY Cust_Name , CONVERT(CHAR(7), Order_Date, 120) ORDER BY 2, 1 --326, ORACLE SELECT Cust_Name , TO_CHAR(Order_Date, 'YYYY-MM') Order_YrMs --方法1 --, TRUNC(Order_Date, 'MM') Order_Date--方法2 , SUM(Qty) Qty FROM Orders_Big WHERE 1=1 AND Cust_Name IN ('A1', 'A2', 'A3') AND Order_Date >= DATE'2009-01-01' AND Order_Date<DATE'2009-02-01' GROUP BY Cust_Name , TO_CHAR(Order_Date, 'YYYY-MM')--方法1 --, TRUNC(Order_Date, 'MM')--方法2 ORDER BY 2, 1
希望本文所述對大家SQL Server數據庫程序設計有所幫助。