在论坛中出现的比较难的sql问题:24(生成时间段)
来源:互联网 发布:淘宝没有指纹支付选项 编辑:程序博客网 时间:2024/06/05 05:49
最近,在论坛中,遇到了不少比较难的sql问题,虽然自己都能解决,但发现过几天后,就记不起来了,也忘记解决的方法了。
所以,觉得有必要记录下来,这样以后再次碰到这类问题,也能从中获取解答的思路。
1、请问我这样的需求如何实现,看示很简单的问题,实现起来很复杂吗?
现在是这样的问题了
USE tempdb IF OBJECT_ID('TEST') IS NOT NULL DROP TABLE TEST; --> 如果表TEST不为空,删除表GO CREATE TABLE TEST --> 创建表,字段及类型如下(CalID varchar(20), --> 编号CalDate datetime, --> 日期字段CalStime datetime, --> 时间字段CalEtime datetime, --> 机器号CalBZ varchar(10) --> 这是机器要求的时间间隔字段,单位分钟); GO INSERT TEST --> 向表中插入数据select 'A01','2011-03-13 00:00:00.000','00:00:00.000','06:59:00.000','丙' union allselect 'A01','2011-03-13 00:00:00.000','07:00:00.000','14:59:00.000','甲' union allselect 'A01','2011-03-13 00:00:00.000','15:00:00.000','21:59:00.000','乙' union allselect 'A01','2011-03-13 00:00:00.000','22:00:00.000','23:59:00.000','丁'GO SELECT * FROM TEST; --> 表的结构如下GO/*CalID CalDate CalStime CalEtime CalBZ -------------------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ---------- A01 2011-03-13 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 06:59:00.000 丙A01 2011-03-13 00:00:00.000 1900-01-01 07:00:00.000 1900-01-01 14:59:00.000 甲A01 2011-03-13 00:00:00.000 1900-01-01 15:00:00.000 1900-01-01 21:59:00.000 乙A01 2011-03-13 00:00:00.000 1900-01-01 22:00:00.000 1900-01-01 23:59:00.000 丁*/--怎么得到以下结果?/*A01 2011-03-13 00:00 2011-03-13 00:29 丙A01 2011-03-13 00:30 2011-03-13 00:59 丙A01 2011-03-13 01:00 2011-03-13 01:29 丙A01 2011-03-13 01:30 2011-03-13 01:59 丙.....以此类推A01 2011-03-13 23:00 2011-03-13 23:29 丁A01 2011-03-13 23:30 2011-03-13 23:59 丁*/
简单的说,就是如何把表中一个时间范围分段成若干个小时间段?在sql server 2000系统中。
我的方法:
select CalID, DATEADD(minute,30*number,cal_stime) calStime, DATEADD(minute,30*(number+1)-1,cal_stime) calEtime, --Cal_Stime, --Cal_Etime, --number, CalBZfrom(select *, convert(varchar(11),CalDate,120)+CONVERT(varchar(5),CalStime,108) Cal_Stime, convert(varchar(11),CalDate,120)+CONVERT(varchar(5),CalEtime,108) Cal_Etime from TEST)t,master..spt_values swhere s.type = 'P'and s.number >=0and DATEADD(MINUTE,30*number,Cal_Stime) <=Cal_Etime/*CalID calStime calEtime CalBZA01 2011-03-13 00:00:00.000 2011-03-13 00:29:00.000 丙A01 2011-03-13 00:30:00.000 2011-03-13 00:59:00.000 丙A01 2011-03-13 01:00:00.000 2011-03-13 01:29:00.000 丙A01 2011-03-13 01:30:00.000 2011-03-13 01:59:00.000 丙A01 2011-03-13 02:00:00.000 2011-03-13 02:29:00.000 丙A01 2011-03-13 02:30:00.000 2011-03-13 02:59:00.000 丙A01 2011-03-13 03:00:00.000 2011-03-13 03:29:00.000 丙A01 2011-03-13 03:30:00.000 2011-03-13 03:59:00.000 丙A01 2011-03-13 04:00:00.000 2011-03-13 04:29:00.000 丙A01 2011-03-13 04:30:00.000 2011-03-13 04:59:00.000 丙A01 2011-03-13 05:00:00.000 2011-03-13 05:29:00.000 丙A01 2011-03-13 05:30:00.000 2011-03-13 05:59:00.000 丙A01 2011-03-13 06:00:00.000 2011-03-13 06:29:00.000 丙A01 2011-03-13 06:30:00.000 2011-03-13 06:59:00.000 丙A01 2011-03-13 07:00:00.000 2011-03-13 07:29:00.000 甲A01 2011-03-13 07:30:00.000 2011-03-13 07:59:00.000 甲A01 2011-03-13 08:00:00.000 2011-03-13 08:29:00.000 甲A01 2011-03-13 08:30:00.000 2011-03-13 08:59:00.000 甲A01 2011-03-13 09:00:00.000 2011-03-13 09:29:00.000 甲A01 2011-03-13 09:30:00.000 2011-03-13 09:59:00.000 甲A01 2011-03-13 10:00:00.000 2011-03-13 10:29:00.000 甲A01 2011-03-13 10:30:00.000 2011-03-13 10:59:00.000 甲A01 2011-03-13 11:00:00.000 2011-03-13 11:29:00.000 甲A01 2011-03-13 11:30:00.000 2011-03-13 11:59:00.000 甲A01 2011-03-13 12:00:00.000 2011-03-13 12:29:00.000 甲A01 2011-03-13 12:30:00.000 2011-03-13 12:59:00.000 甲A01 2011-03-13 13:00:00.000 2011-03-13 13:29:00.000 甲A01 2011-03-13 13:30:00.000 2011-03-13 13:59:00.000 甲A01 2011-03-13 14:00:00.000 2011-03-13 14:29:00.000 甲A01 2011-03-13 14:30:00.000 2011-03-13 14:59:00.000 甲A01 2011-03-13 15:00:00.000 2011-03-13 15:29:00.000 乙A01 2011-03-13 15:30:00.000 2011-03-13 15:59:00.000 乙A01 2011-03-13 16:00:00.000 2011-03-13 16:29:00.000 乙A01 2011-03-13 16:30:00.000 2011-03-13 16:59:00.000 乙A01 2011-03-13 17:00:00.000 2011-03-13 17:29:00.000 乙A01 2011-03-13 17:30:00.000 2011-03-13 17:59:00.000 乙A01 2011-03-13 18:00:00.000 2011-03-13 18:29:00.000 乙A01 2011-03-13 18:30:00.000 2011-03-13 18:59:00.000 乙A01 2011-03-13 19:00:00.000 2011-03-13 19:29:00.000 乙A01 2011-03-13 19:30:00.000 2011-03-13 19:59:00.000 乙A01 2011-03-13 20:00:00.000 2011-03-13 20:29:00.000 乙A01 2011-03-13 20:30:00.000 2011-03-13 20:59:00.000 乙A01 2011-03-13 21:00:00.000 2011-03-13 21:29:00.000 乙A01 2011-03-13 21:30:00.000 2011-03-13 21:59:00.000 乙A01 2011-03-13 22:00:00.000 2011-03-13 22:29:00.000 丁A01 2011-03-13 22:30:00.000 2011-03-13 22:59:00.000 丁A01 2011-03-13 23:00:00.000 2011-03-13 23:29:00.000 丁A01 2011-03-13 23:30:00.000 2011-03-13 23:59:00.000 丁*/
2、如何取0点开始至今的时间段集合, 间隔1小时
http://bbs.csdn.net/topics/390706187
如题:如何取0点开始至今的时间段集合, 间隔1小时
0:00 - 1:00
1:00 - 2:00
...
23:00 - 0:00
下面的代码产生的是纯时间段,不过不好用:
select convert(varchar(10),getdate(),120) '当天日期', convert(varchar(5),dateadd(hour,s.number,convert(varchar(10),getdate(),120)),108)+'-'+ convert(varchar(5),dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)),108) '时间段'from master..spt_values swhere s.type = 'P' and s.number <= 23/*当天日期时间段2014-02-1100:00-01:002014-02-1101:00-02:002014-02-1102:00-03:002014-02-1103:00-04:002014-02-1104:00-05:002014-02-1105:00-06:002014-02-1106:00-07:002014-02-1107:00-08:002014-02-1108:00-09:002014-02-1109:00-10:002014-02-1110:00-11:002014-02-1111:00-12:002014-02-1112:00-13:002014-02-1113:00-14:002014-02-1114:00-15:002014-02-1115:00-16:002014-02-1116:00-17:002014-02-1117:00-18:002014-02-1118:00-19:002014-02-1119:00-20:002014-02-1120:00-21:002014-02-1121:00-22:002014-02-1122:00-23:002014-02-1123:00-00:00*/
这个是产生日期时间段,比较实用:
if OBJECT_ID('tempdb..#seTable') is not null drop table #seTable create table #seTable(starTime nvarchar(40),endTime nvarchar(40))insert into #seTableselect --convert(varchar(10),getdate(),120) curr_date, convert(nvarchar(19),dateadd(hour,s.number,convert(varchar(10),getdate(),120)),120) start_date, convert(nvarchar(19),dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)),120) end_datefrom master..spt_values swhere s.type = 'P' and s.number <= 23select *from #seTable/*starTimeendTime2014-02-11 00:00:002014-02-11 01:00:002014-02-11 01:00:002014-02-11 02:00:002014-02-11 02:00:002014-02-11 03:00:002014-02-11 03:00:002014-02-11 04:00:002014-02-11 04:00:002014-02-11 05:00:002014-02-11 05:00:002014-02-11 06:00:002014-02-11 06:00:002014-02-11 07:00:002014-02-11 07:00:002014-02-11 08:00:002014-02-11 08:00:002014-02-11 09:00:002014-02-11 09:00:002014-02-11 10:00:002014-02-11 10:00:002014-02-11 11:00:002014-02-11 11:00:002014-02-11 12:00:002014-02-11 12:00:002014-02-11 13:00:002014-02-11 13:00:002014-02-11 14:00:002014-02-11 14:00:002014-02-11 15:00:002014-02-11 15:00:002014-02-11 16:00:002014-02-11 16:00:002014-02-11 17:00:002014-02-11 17:00:002014-02-11 18:00:002014-02-11 18:00:002014-02-11 19:00:002014-02-11 19:00:002014-02-11 20:00:002014-02-11 20:00:002014-02-11 21:00:002014-02-11 21:00:002014-02-11 22:00:002014-02-11 22:00:002014-02-11 23:00:002014-02-11 23:00:002014-02-12 00:00:00*/
0 0
- 在论坛中出现的比较难的sql问题:24(生成时间段)
- 在论坛中出现的比较难的sql问题:8(递归问题 树形结构分组)
- 在论坛中出现的比较难的sql问题:12(递归问题2 拆分字符串)
- 在论坛中出现的比较难的sql问题:23(随机填充问题)
- 在论坛中出现的比较难的sql问题:30(row_number函数 物料组合问题)
- 在论坛中出现的比较难的sql问题:33(递归 连续日期问题 )
- 在论坛中出现的比较难的sql问题:34(递归 获取连续值问题)
- 在论坛中出现的比较难的sql问题:35(时间间隔计算问题)
- 在论坛中出现的比较难的sql问题:38(字符拆分 字符串检索问题)
- 在论坛中出现的比较难的sql问题:2(row_number函数+子查询)
- 在论坛中出现的比较难的sql问题:3(row_number函数 分组查询)
- 在论坛中出现的比较难的sql问题:16(取一个字段中的数字)
- 在论坛中出现的比较难的sql问题:17(字符分拆2)
- 在论坛中出现的比较难的sql问题:20(触发器专题2)
- 在论坛中出现的比较难的sql问题:22(触发器专题3)
- 在论坛中出现的比较难的sql问题:25(字符串拆分3)
- 在论坛中出现的比较难的sql问题:41(循环替换 循环替换关键字)
- 在论坛中出现的比较难的sql问题:46(日期条件出现的奇怪问题)
- 物化视图刷新方式
- ngnix和tomcat日志
- Live555 流程上的一些分析
- java nio学习记录2---详解
- smack/asmack 监听特定报文代码
- 在论坛中出现的比较难的sql问题:24(生成时间段)
- Halcon简介
- Android类库--与Adapter相关的类
- WPF +MVVM模式下的增删改查
- getHibernateTemplate常用方法
- openstack:nova中的几个问题分析
- “资本公积”与“盈余公积”的区别
- web前端+jsp页面中使用hidden来传值
- 优秀文字摘抄