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