Oracle查询1-12月数据

来源：互联网发布：2017优化设计英语答案编辑：程序博客网时间：2024/04/30 16:17

--模拟数据表 create table [TB]([id] int,[time] datetime,[count] int) --添加模拟数据 insert [TB] select 1,'2012-12-01 02:00:00',42 union all select 1,'2012-12-01 14:18:12',79 union all select 1,'2012-12-10 07:15:42',112 union all select 1,'2012-12-14 10:06:20',57 union all select 1,'2012-12-14 11:54:10',124 union all select 1,'2012-12-14 14:10:27',241  --根据日前的月份和天数分组求和， --条件为当前的月份转换成2012-12-14 00:00:00 的时间格式 --month()、day()函数返回值为整型，故转换为字符后拼接 --数据格式为2012-12-14 00:00:00可正常运行 --如果没有12号数据则不统计 SELECT CAST(MONTH([time]) AS VARCHAR(4))+'-'+RIGHT('00'+CAST(DAY([time]) AS VARCHAR(4)),2) AS [time],SUM([count]) AS SumCount FROM TBWHERE  CONVERT(VARCHAR(7),[time],120)=CONVERT(VARCHAR(7),GETDATE(),120)GROUP BY MONTH([time]),DAY([time])--删除模拟数据DROP TABLE TB--结果time      SumCount--------- -----------12-01     12112-10     11212-14     422

--模拟数据表 create table [TB]([id] int,[time] datetime,[count] int) --添加模拟数据 insert [TB] select 1,'2012-12-01 02:00:00',42 union all select 1,'2012-12-01 14:18:12',79 union all select 1,'2012-12-10 07:15:42',112 union all select 1,'2012-12-14 10:06:20',57 union all select 1,'2012-12-14 11:54:10',124 union all select 1,'2012-12-14 14:10:27',241  --声明空白数据DECLARE @tbnull TABLE(id INT,[time] DATETIME,[count] INT)--递增变量DECLARE @day INTSET @day=0--当月天数DECLARE @days INTSELECT @days= day(dateadd(mm,1,getdate())-day(getdate())) --循环插入空白数据WHILE (@day<@days)BEGINSET @day=@day+1INSERT INTO @tbnull        ( id, time, count )VALUES  ( 1,CONVERT(DATETIME,CONVERT(VARCHAR(8),GETDATE(),120)+RIGHT('00'+@day,2)),0)END; --合并数据 WITH data AS (    SELECT * FROM TB    UNION ALL     SELECT * FROM @tbnull ) --根据日前的月份和天数分组求和， --条件为当前的月份转换成2012-12-14 00:00:00 的时间格式 --month()、day()函数返回值为整型，故转换为字符后拼接 --数据格式为2012-12-14 00:00:00可正常运行 SELECT CAST(MONTH([time]) AS VARCHAR(4))+'-'+RIGHT('00'+CAST(DAY([time]) AS VARCHAR(4)),2) AS [time],    SUM([count]) AS SumCount FROM data    WHERE  CONVERT(VARCHAR(7),[time],120)=CONVERT(VARCHAR(7),GETDATE(),120)    GROUP BY MONTH([time]),DAY([time])--删除模拟数据DROP TABLE     TB--结果time      SumCount--------- -----------12-01     12112-02     012-03     012-04     012-05     012-06     012-07     012-08     012-09     012-10     11212-11     012-12     012-13     012-14     42212-15     012-16     012-17     012-18     012-19     012-20     012-21     012-22     012-23     012-24     012-25     012-26     012-27     012-28     012-29     012-30     012-31     0

0 0