SQL Server简单实现数据的日报和月报功能

本文实例讲述了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数据库程序设计有所帮助。

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 SQL Server简单实现数据的日报和月报功能