SQL Server简单实现数据的日报和月报功能
本文实例讲述了SQLServer简单实现数据的日报和月报功能。分享给大家供大家参考，具体如下：
--320,SQLSERVER日报
--查询2009-01-01当天客户A1,A2,A3的订单数量
SELECTCust_Name
,CONVERT(CHAR(10),Order_Date,120)Order_Date
,SUM(Qty)Qty
FROMOrders_Big
WHERE1=1
ANDCust_NameIN('A1','A2','A3')
ANDOrder_Date>='2009-01-01'
ANDOrder_Date<'2009-01-02'
/*ANDOrder_Date--BETWEEN...AND相当于>=和<=
BETWEEN'2009-01-01'
AND'2009-01-02'*/
GROUPBYCust_Name
,CONVERT(CHAR(10),Order_Date,120)
ORDERBY2,1
--321.ORACLE日报
--查询2009-01-01当天客户A1,A2,A3的订单数量
SELECTCust_Name
,TO_CHAR(Order_Date,'YYYY-MM-DD')Order_Date--方法一:转换成字符串类型
--,TRUNC(Order_Date)Order_Date--方法二:将时分秒信息截断
,SUM(Qty)Qty
FROMOrders_Big
WHERE1=1
ANDCust_NameIN('A1','A2','A3')
ANDOrder_Date>=DATE'2009-01-01'
ANDOrder_Date<DATE'2009-01-02'
GROUPBYCust_Name
,TO_CHAR(Order_Date,'YYYY-MM-DD')--方法一
--,TRUNC(Order_Date)--方法二
ORDERBY2,1
------------------------------10.2------------------------------------
--326,SQLSERVER月报
SELECTCust_Name
,CONVERT(CHAR(7),Order_Date,120)Order_YrMs
,CAST(CONVERT(CHAR(7),Order_Date,120)+'-01'ASDATETIME)Order_Date
,SUM(Qty)Qty
FROMOrders_Big
WHERE1=1
ANDCust_NameIN('A1','A2','A3')
ANDOrder_Date>='2009-01-01'
ANDOrder_Date<'2009-02-01'
GROUPBYCust_Name
,CONVERT(CHAR(7),Order_Date,120)
ORDERBY2,1
--326,ORACLE
SELECTCust_Name
,TO_CHAR(Order_Date,'YYYY-MM')Order_YrMs--方法1
--,TRUNC(Order_Date,'MM')Order_Date--方法2
,SUM(Qty)Qty
FROMOrders_Big
WHERE1=1
ANDCust_NameIN('A1','A2','A3')
ANDOrder_Date>=DATE'2009-01-01'
ANDOrder_Date<DATE'2009-02-01'
GROUPBYCust_Name
,TO_CHAR(Order_Date,'YYYY-MM')--方法1
--,TRUNC(Order_Date,'MM')--方法2
ORDERBY2,1
希望本文所述对大家SQLServer数据库程序设计有所帮助。