按时间分段汇总
来源：互联网 发布：jquery1.11.1.js下载 编辑：程序博客网 时间：2024/05/23 22:18
CREATE TABLE #tb(列名1 varchar(12),时间 datetime)INSERT INTO #tbSELECT '03174190188','2009-11-01 07:17:39.217' UNION ALLSELECT '015224486575','2009-11-01 08:01:17.153' UNION ALLSELECT '013593006926','2009-11-12 08:04:46.560' UNION ALLSELECT '013599584239','2009-11-22 08:53:27.763' UNION ALLSELECT '013911693526','2009-11-23 08:53:51.683' UNION ALLSELECT '013846472440','2009-11-23 08:54:57.233' UNION ALLSELECT '013990353697','2009-11-24 08:55:25.077' UNION ALLSELECT '013990353697','2009-11-25 08:56:01.327' UNION ALLSELECT '013945594843','2009-11-26 08:57:02.233' UNION ALLSELECT '013990353697','2009-11-27 08:57:29.700' UNION ALLSELECT '013916597421','2009-11-28 08:59:49.390' UNION ALLSELECT '03916995857','2009-11-29 09:11:05.607' UNION ALLSELECT '015097712001','2009-11-30 09:13:50.293'DECLARE @minDate datetime,@maxDate datetime;SELECT @minDate = '2009-11-1',@maxDate = '2009-12-01';DECLARE @sql varchar(8000);SET @sql = '';SELECT @sql=@sql+',SUM(CASE WHEN DATEDIFF(day,B.时间,''' +CONVERT(varchar(10),DATEADD(day,number,@minDate),120) +''')=0 THEN 1 ELSE 0 END) AS [' +CONVERT(varchar(10),DATEADD(day,number,@minDate),120)+']'FROM master.dbo.spt_values WHERE type = 'P' AND DATEADD(day,number,@minDate)<=@maxDate;print @sqlDECLARE @cmd nvarchar(4000);SET @cmd = N'SELECT ISNULL(A.时段,''合计'') AS 时段'+@sql+', COUNT(列名1) AS 合计FROM( SELECT 时段=RIGHT(100+number,2)+'':00~''+RIGHT(100+number+1,2)+'':00'', MinDate = RIGHT(100+number,2)+'':00:00'', MaxDate = RIGHT(100+number+1,2)+'':00:00'' FROM master.dbo.spt_values WHERE type = ''P'' AND number < 24) AS A LEFT JOIN (SELECT * FROM #tb WHERE 时间 BETWEEN @minDate AND @maxDate) AS BON CONVERT(varchar(8),B.时间,108) >= A.MinDate AND CONVERT(varchar(8),B.时间,108) < A.MaxDateGROUP BY A.时段 WITH ROLLUP;'EXEC sp_executesql @cmd,N'@minDate datetime,@maxDate datetime',@minDate,@maxDate;print @cmdDROP TABLE #tb; /*时段 2009-11-01 2009-11-02 2009-11-03 2009-11-04 2009-11-05 2009-11-06 2009-11-07 2009-11-08 2009-11-09 2009-11-10 2009-11-11 2009-11-12 2009-11-13 2009-11-14 2009-11-15 2009-11-16 2009-11-17 2009-11-18 2009-11-19 2009-11-20 2009-11-21 2009-11-22 2009-11-23 2009-11-24 2009-11-25 2009-11-26 2009-11-27 2009-11-28 2009-11-29 2009-11-30 2009-12-01 合计--------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------00:00~01:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 001:00~02:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 002:00~03:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 003:00~04:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 004:00~05:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 005:00~06:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 006:00~07:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 007:00~08:00 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 108:00~09:00 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 2 1 1 1 1 1 0 0 0 1009:00~10:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 210:00~11:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 011:00~12:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 012:00~13:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 013:00~14:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 014:00~15:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 015:00~16:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 016:00~17:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 017:00~18:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 018:00~19:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 019:00~20:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 020:00~21:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 021:00~22:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 022:00~23:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 023:00~24:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0合计 2 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 2 1 1 1 1 1 1 1 0 13警告: 聚合或其他 SET 操作消除了 Null 值。(25 行受影响)*/
- 按时间分段汇总
- [.NET] EF LINQ 按时间对数据分类汇总
- 如何按时间段来汇总数据
- access按时间统计
- 按时间分组查询
- mysql 按时间查询
- 按时间计算
- logcat按时间排序
- mysql 按时间查询
- mysql 按时间查询
- ls 按时间排序
- sql 按时间查询
- MySQL按时间分组
- mysql按时间统计
- json按时间排序
- 按时间读取歌词
- 按时间生成订单号
- 按时
- 使用nohup在后台运行scp
- Java 匿名内部类的示例介绍
- SQL语句优化技术简要分析
- Qt 动画详解一
- Handler的用法
- 按时间分段汇总
- Spring2.x中设置Bean的作用域
- UE正则表达式语法及常用语句
- 多行文本编辑时，不显示不能完整显示的行
- 在EF4.1的DBContext中实现事务处理（BeginTransaction）和直接执行SQL语句的示例
- opencv函数之RNG
- 虚拟机中vmware tools
- 点击按钮切换相应的内容，可用于转换图片
- 在php中使用mysql
