22,301
社区成员
发帖
与我相关
我的任务
分享求某个年月内每天的销售总和
订单表，有以下几个主要主段
ID 主键
PTIME 时间，格式如2013/9/11 15:17:01
PNUM 金额
记录如：
1 2014/8/1 15:17:01 125
2 2014/8/2 15:17:01 100.10
3 2014/8/2 15:17:01 200
4 2014/8/3 15:17:01 158
5 2013/8/5 15:17:01 130
............................
页面传过来两个值．一个是年份，一个是月份
比如：a.aspx?year=2014&month=8
想要得到2014年8月．1-31号每天的销售总额？
想要的结果是
1号 125
2号 300.10
3号 158
4号 0
5号 130
求大神帮忙！
ID 主键
PTIME 时间，格式如2013/9/11 15:17:01
PNUM 金额
记录如：
1 2014/8/1 15:17:01 125
2 2014/8/2 15:17:01 100.10
3 2014/8/2 15:17:01 200
4 2014/8/3 15:17:01 158
5 2013/8/5 15:17:01 130
............................
页面传过来两个值．一个是年份，一个是月份
比如：a.aspx?year=2014&month=8
想要得到2014年8月．1-31号每天的销售总额？
想要的结果是
1号 125
2号 300.10
3号 158
4号 0
5号 130
求大神帮忙！
...全文
请发表友善的回复…
发表回复
發糞塗牆 2014-08-28
- 打赏
- 举报
对，所以我说最好建一个实体表，然后存个2、3年，这样你就不用每次都传入值来动态生成这个时间表
fxxyz 2014-08-28
- 打赏
- 举报
SET @startdate = '2008-01-01'
SET @enddate = '2009-12-31'
但这两个时间段．enddate必须大于我输入的年份和月份．否则就取不到数据的．
DECLARE @startdate DATETIME , @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-12-31'
DECLARE @year CHAR(4)
SET @year='2014'
DECLARE @month VARCHAR(2)
SET @month='08';
WITH cte AS (
SELECT
DISTINCT
YEAR(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [year] ,
MONTH(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [month] ,
DAY(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [day]
FROM master..spt_values
WHERE DATEDIFF(day, DATEADD(day, number, @startdate), @enddate) >= 0
AND number >= 0
AND type = 'p' AND YEAR(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120))=@year AND
MONTH(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120))=@month)
select CAST(cte.[day] AS VARCHAR(2))+'号' 日期,SUM(ISNULL(order_amount,0))order_amount
from ak_orders RIGHT JOIN cte ON DATEPART(YEAR,payment_time)=cte.year AND DATEPART(MONTH,payment_time)=cte.MONTH
AND DATEPART(day,payment_time)=cte.day
GROUP BY CAST(cte.[day] AS VARCHAR(2))+'号', cte.[day]
ORDER BY cte.[day]發糞塗牆 2014-08-28
- 打赏
- 举报
大哥，这个AND type = 'p'是什么意思？[/quote]这个你不用太过在意，要研究的话可以看看master..spt_values这个表的数据，P是限制只取连续的1~2047的值而已。[/quote]
有个问题一直不是特别理解
SET @startdate = '2008-01-01'
SET @enddate = '2009-12-31'
这两个值．是我传值过来的吧？
按理说．这个enddate是变动的．比如现在是09年12月31号．那明天就变成10-1-1号了．这个时间跟下面的
某年某月．起的作用是什么？
我只能大概理解是这两个变量是某个时间段内．而要取值的时间段必须是这两个时间内的．是吧？[/quote]这两个值是创造一个时间区间的年月日列表用的，跟你的需求其实没多大关系，创造这个列表是为了当你的数据某些日期没有销售记录时，依旧能带出这些日期用的
hleb231 2014-08-28
- 打赏
- 举报
一个开始日期 ，一个结束日期 ，代表你要查询哪段时间内的记录。都是可以变动的啦。
fxxyz 2014-08-28
- 打赏
- 举报
大哥，这个AND type = 'p'是什么意思？[/quote]这个你不用太过在意，要研究的话可以看看master..spt_values这个表的数据，P是限制只取连续的1~2047的值而已。[/quote]
有个问题一直不是特别理解
SET @startdate = '2008-01-01'
SET @enddate = '2009-12-31'
这两个值．是我传值过来的吧？
按理说．这个enddate是变动的．比如现在是09年12月31号．那明天就变成10-1-1号了．这个时间跟下面的
某年某月．起的作用是什么？
我只能大概理解是这两个变量是某个时间段内．而要取值的时间段必须是这两个时间内的．是吧？
xiaodongni 2014-08-27
- 打赏
- 举报
看了各位大神的回复 再看看自己的代码。没有勇气点提交回复了。
o2_ 2014-08-27
- 打赏
- 举报
学习了
發糞塗牆 2014-08-27
- 打赏
- 举报
这个你直接用就好了
發糞塗牆 2014-08-27
- 打赏
- 举报
大哥，这个AND type = 'p'是什么意思？[/quote]这个你不用太过在意，要研究的话可以看看master..spt_values这个表的数据，P是限制只取连续的1~2047的值而已。
fxxyz 2014-08-27
- 打赏
- 举报
大哥，这个AND type = 'p'是什么意思？
KeepSayingNo 2014-08-27
- 打赏
- 举报
这个只能获取有记录的日期内的数据呀[/quote]
你如果要这个月每一天都有一条记录，那你就需要根据我给你写的脚本从@begin_time和@end_time生成1到31的记录
可以这样：
declare @year varchar(5)
declare @month varchar(5)
declare @begin_time datetime
declare @end_time datetime
declare @sql varchar(256)
set @year='2014'
set @month='8'
set @begin_time=@year+'/'+@month+'/'+'1 00:00:00'
select @end_time= dateadd(ss,-1,DATEADD(M,1,@begin_time))
IF object_id('tempdb..#tempDate') is not null
BEGIN
drop table #tempDate
END
CREATE table #tempDate
(
stat_day varchar(10)
)
CREATE clustered index tempDate_Index1 on #tempDate (stat_day)
declare @time_temp datetime
set @time_temp = @begin_time
while @time_temp < @end_time
begin
insert into #tempDate (stat_day) values (convert(varchar(5),day(@time_temp))+'号')
set @time_temp= dateadd(d,1,@time_temp)
end
select * from #tempDate
set @sql='
select a.stat_day, isnull(b.total,0) from #tempDate a
left join(
SELECT Cast(Datepart(dd, PTIME) AS VARCHAR) + ''号'' as stat_day,
Sum(PNUM) as total
FROM 订单表
WHERE PTIME between '+@begin_time+' and '+@end_time+'
GROUP BY Cast(Datepart(dd, PTIME) AS VARCHAR) + ''号'')
b on a.stat_day=b.stat_day'
exec(@sql)
drop table #tempDate
發糞塗牆 2014-08-27
- 打赏
- 举报
实际的功能．是页面上．有两个年月的下拉列表．选择年份和月份以后．会获取这个年月下面每一天的销售总和
年月值是从其他地方传过来的．[/quote]这没关系啊，反正你要左联或者右连
KeepSayingNo 2014-08-27
- 打赏
- 举报
之前有个网友就说过时间处理效率的问题导致查询大量数据会很慢，帮你优化了下
declare @year varchar(5)
declare @month varchar(5)
declare @begin_time datetime
declare @end_time datetime
declare @sql varchar(256)
set @year='2014'
set @month='8'
set @begin_time=@year+'/'+@month+'/'+'1 00:00:00'
select @end_time= dateadd(ss,-1,DATEADD(M,1,@begin_time))
set @sql='SELECT Cast(Datepart(dd, PTIME) AS VARCHAR) + ''号'',
Sum(PNUM)
FROM 订单表
WHERE PTIME between '+@begin_time+' and '+@end_time+'
GROUP BY Cast(Datepart(dd, PTIME) AS VARCHAR) + ''号''
fxxyz 2014-08-27
- 打赏
- 举报
实际的功能．是页面上．有两个年月的下拉列表．选择年份和月份以后．会获取这个年月下面每一天的销售总和
年月值是从其他地方传过来的．
fxxyz 2014-08-27
- 打赏
- 举报
这个只能获取有记录的日期内的数据呀
發糞塗牆 2014-08-27
- 打赏
- 举报
个人建议，如果你需要经常性这样做，可以先建一个实体表，把1、2年的年月日算出来，然后存到这个实体表里面，下次要用的时候直接join，不用每次都动态生成。大概如下，生成 '2008-01-01'到 '2009-12-31'之间的年月日数据
DECLARE @startdate DATETIME ,
@enddate DATETIME
SET @startdate = '2008-01-01'
SET @enddate = '2009-12-31'
SELECT
DISTINCT
YEAR(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [year] ,
MONTH(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [month] ,
DAY(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [day]
FROM master..spt_values
WHERE DATEDIFF(day, DATEADD(day, number, @startdate), @enddate) >= 0
AND number >= 0
AND type = 'p'發糞塗牆 2014-08-27
- 打赏
- 举报
DECLARE @startdate DATETIME , @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-12-31'
DECLARE @year CHAR(4)
SET @year='2014'
DECLARE @month VARCHAR(2)
SET @month='08'
;WITH cte AS (
SELECT
DISTINCT
YEAR(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [year] ,
MONTH(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [month] ,
day(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [day]
FROM master..spt_values
WHERE DATEDIFF(day, DATEADD(day, number, @startdate), @enddate) >= 0
AND number >= 0
AND type = 'p' AND YEAR(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120))=@year AND
MONTH(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120))=@month)
这段是创建一个日期表，把8月或者说参数锁在的年月日带出来
發糞塗牆 2014-08-27
- 打赏
- 举报
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-27 15:43:45
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[Tb]
if object_id('[Tb]') is not null drop table [Tb]
go
create table [Tb]([id] int,[ptime] datetime,[pnum] numeric(5,2))
insert [Tb]
select 1,'2014/8/1 15:17:01',125 union all
select 2,'2014/8/2 15:17:01',100.10 union all
select 3,'2014/8/2 15:17:01',200 union all
select 4,'2014/8/3 15:17:01',158 union all
select 5,'2013/8/5 15:17:01',130
--------------开始查询--------------------------
DECLARE @startdate DATETIME , @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-12-31'
DECLARE @year CHAR(4)
SET @year='2014'
DECLARE @month VARCHAR(2)
SET @month='08'
;WITH cte AS (
SELECT
DISTINCT
YEAR(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [year] ,
MONTH(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [month] ,
day(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120)) AS [day]
FROM master..spt_values
WHERE DATEDIFF(day, DATEADD(day, number, @startdate), @enddate) >= 0
AND number >= 0
AND type = 'p' AND YEAR(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120))=@year AND
MONTH(CONVERT(VARCHAR(10), DATEADD(day, number, @startdate), 120))=@month)
select CAST(cte.[day] AS VARCHAR(2))+'号' 日期,SUM(ISNULL([pnum],0))[pnum]
from [Tb] RIGHT JOIN cte ON DATEPART(YEAR,ptime)=cte.year AND DATEPART(MONTH,ptime)=cte.MONTH
AND DATEPART(day,ptime)=cte.day
GROUP BY CAST(cte.[day] AS VARCHAR(2))+'号', cte.[day]
ORDER BY cte.[day]
----------------结果----------------------------
/*
日期 pnum
---- ---------------------------------------
1号 125.00
2号 300.10
3号 158.00
4号 0.00
5号 0.00
6号 0.00
7号 0.00
8号 0.00
9号 0.00
10号 0.00
11号 0.00
12号 0.00
13号 0.00
14号 0.00
15号 0.00
16号 0.00
17号 0.00
18号 0.00
19号 0.00
20号 0.00
21号 0.00
22号 0.00
23号 0.00
24号 0.00
25号 0.00
26号 0.00
27号 0.00
28号 0.00
29号 0.00
30号 0.00
31号 0.00
*/
向东流 2014-08-27
- 打赏
- 举报
SELECT Cast(Datepart(dd, PTIME) AS VARCHAR) + '号',
Sum(PNUM)
FROM 订单表
WHERE Year(PTIME) = @year
AND Month(PTIME) = @month
GROUP BY Cast(Datepart(dd, PTIME) AS VARCHAR) + '号'
