求一SQL语句.
字段说明:
Order_Total :订单金额 数据类型Money
CreateDaTe:订单建立时间 数据类型Datetime 存储日期时间类似'2006-10-01 00:00:00'
要求:
当用户输入建立时间大于'2006-10-01 00:00:00' 到 '2006-10-30 23:59:59'时
显示每10月1号到10月30号的每一天订单建立的订单共有几单,每一天的订单的总金额是多少?
比方说:
年, 月, 日, 订单总数, 订单总和
2006 10 1 5 5000
2006 10 2 6 9000
2006 10 3 9 35000
........
现小弟写的语句为
Select
Year(Convert(varchar(10), CreateDate, 120)) As '年',
Month(Convert(varchar(10), CreateDate, 120)) As '月',
Day(Convert(varchar(10), CreateDate, 120)) As '日',
Count(Convert(varchar(10), CreateDate, 120)) As '订单总数',
Sum(Order_Total) As '订单总和'
From [Order]
where Convert(varchar(10), CreateDate, 120) >= '2006-11-01'
AND Convert(varchar(10), CreateDate, 120) <= '2006-11-19'
Group By Convert(varchar(10), CreateDate, 120),CreateDate
ORDER BY '年','月','日',Sum(Order_Total)
但是结果不对,请牛人赐教!
字段说明:
Order_Total :订单金额 数据类型Money
CreateDaTe:订单建立时间 数据类型Datetime 存储日期时间类似'2006-10-01 00:00:00'
要求:
当用户输入建立时间大于'2006-10-01 00:00:00' 到 '2006-10-30 23:59:59'时
显示每10月1号到10月30号的每一天订单建立的订单共有几单,每一天的订单的总金额是多少?
比方说:
年, 月, 日, 订单总数, 订单总和
2006 10 1 5 5000
2006 10 2 6 9000
2006 10 3 9 35000
........
现小弟写的语句为
Select
Year(Convert(varchar(10), CreateDate, 120)) As '年',
Month(Convert(varchar(10), CreateDate, 120)) As '月',
Day(Convert(varchar(10), CreateDate, 120)) As '日',
Count(Convert(varchar(10), CreateDate, 120)) As '订单总数',
Sum(Order_Total) As '订单总和'
From [Order]
where Convert(varchar(10), CreateDate, 120) >= '2006-11-01'
AND Convert(varchar(10), CreateDate, 120) <= '2006-11-19'
Group By Convert(varchar(10), CreateDate, 120),CreateDate
ORDER BY '年','月','日',Sum(Order_Total)
但是结果不对,请牛人赐教!
解决方案 »
- SQLserver 中怎样用 T-SQL语句插入新一列???求解?
- 关于全文检索和数据库性能的问题,进来的都有分
- 关于sqlserver2005的数据库(初级)
- 用脚本从一个SQLServer连另一个SQLServer的问题
- 存储过程sql语句拼合有错,但不知道怎么改,希望帮忙改一下。
- 又遇SQL难题,求高手来救啊!(邹大侠!!!!!!&其它大侠!!!!!)
- 从EXCEL中导入数据库中时.出现日期格式不对如何处理?急.在线等.
- sqlserver能不能提供这种功能?
- SQL SERVER 2000下,如何准确地获得SQL的安装路径及数据路径?
- 安装出了问题,在线恭候指教
- 求sql语句??
- 为何有的进程会阻塞自己??
年月日 订单总数 订单总和
2006-10-01 5 5000
2006-10-02 6 9000
2006-10-03 9 35000
Year(CreateDate) As '年',
Month(CreateDate) As '月',
Day(CreateDate) As '日',
Count(*) As '订单总数',
Sum(Order_Total) As '订单总和'
From [Order]
where Convert(varchar(10), CreateDate, 120) >= '2006-11-01'
AND Convert(varchar(10), CreateDate, 120) <= '2006-11-19'
Group By Convert(varchar(10), CreateDate, 120),CreateDate
ORDER BY '年','月','日',Sum(Order_Total)
年月日 订单总数 订单总和
2006-10-01 5 5000
2006-10-02 6 9000
2006-10-03 9 35000
Year(Convert(varchar(10), CreateDate, 120)) As '年',
Month(Convert(varchar(10), CreateDate, 120)) As '月',
Day(Convert(varchar(10), CreateDate, 120)) As '日',
Count(Convert(varchar(10), CreateDate, 120)) As '订单总数',
Sum(Order_Total) As '订单总和'
From [Order]
where Convert(varchar(10), CreateDate, 120) >= '2006-11-01'
AND Convert(varchar(10), CreateDate, 120) <= '2006-11-19'
Group By Convert(varchar(10), CreateDate, 120)--去掉,CreateDate
ORDER BY '年','月','日',Sum(Order_Total)
(
select convert(varchar(10),CreateDaTe,120) as 年月日, count(*) as 订单总数, sum(Order_Total) as 订单总和 from TB group by convert(varchar(10),CreateDaTe,120)
) m就是你的结果.
(
select convert(varchar(10),CreateDaTe,120) as 年月日, count(*) as 订单总数, sum(Order_Total) as 订单总和 from TB where CreateDaTe >= '2006-10-01 00:00:00' and CreateDaTe <= '2006-10-30 23:59:59' group by convert(varchar(10),CreateDaTe,120)
) m就是你的结果.
(
select convert(varchar(10),CreateDaTe,120) as 年月日, count(*) as 订单总数, sum(Order_Total) as 订单总和
from [order] where CreateDaTe >= '2006-09-01 00:00:00' and CreateDaTe <= '2006-10-30 23:59:59'
group by convert(varchar(10),CreateDaTe,120)
) m你的语句是对的,但是我很奇怪,为什么我的SQL语句为错呢,是不是CreateDaTe里存储的日期包括了时,分,秒,所以用group子句时,结果不对?
是你截取到秒,所以是按秒分组.
to dawugui(潇洒老乌龟)
你的QQ多少或者MSN.交个朋友.
我的QQ是
99760351