22,206
社区成员
发帖
与我相关
我的任务
分享
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]
这整个代码都是当成SQL语句来执行的么?
如果写成存储过程.要怎么设置这一共四个变量呀?
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
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) + ''号''
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'
----------------------------------------------------------------
-- 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
*/
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) + '号'