在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
atitit.判断时间重叠方法总结 java c++ c#.net js php
2. 重叠算法 实际上就是日期集合跟个时间集合的的交集(乘法算法) 1
先判断日期重叠,在判断时间区段重叠。 每个区段都有内包含,外包含,左包含,右包括...所以,or表达式需要4*4=16个..每个or 表达式包括4个and表达式( 两个日期表达式,两个时间范围表达式) 最终的最终的的表达式需要16*4=64个,,比较长的了..
作者:: 老哇的爪子 Attilax 艾龙, EMAIL:[email protected] 转载请注明来源: http://blog.csdn.net/attilax
2. 重叠算法 实际上就是日期集合跟个时间集合的的交集(乘法算法)在程序设计里有。。。。。 回答 那实际上就是交、并、差。
3. 代码---private void ini() { String db_start_fld = "start_time"; String db_end_fld = "end_time"; String db_include_exp = "db_start_fld>=ui_start and db_end_fld<=ui_end "; String db_outclude_exp = "db_start_fld<=ui_start and db_end_fld>=ui_end "; String db_leftinclude_exp = "db_end_fld>=ui_start and db_end_fld <=ui_end "; String db_ritinclude_exp = "db_start_fld>=ui_start and db_end_fld<=ui_end ";
List<String> dateExpList = new ArrayList<String>() { {
add(expandFunc(db_include_exp)); add(expandFunc(db_outclude_exp)); add(expandFunc(db_leftinclude_exp)); add(expandFunc(db_ritinclude_exp));
}
private String expandFunc(String db_include_exp) { String db_start_fld_Datefunc = " CONVERT(varchar(100), db_start_fld, 23)"; String db_end_fld_Datefunc = " CONVERT(varchar(100), db_end_fld, 23)"; return db_include_exp.replaceAll("db_start_fld", db_start_fld_Datefunc).replaceAll("db_end_fld", db_end_fld_Datefunc); } };
List<String> timeExpList = new ArrayList<String>() { { add(expandFunc(db_include_exp)); add(expandFunc(db_outclude_exp)); add(expandFunc(db_leftinclude_exp)); add(expandFunc(db_ritinclude_exp));
}
private String expandFunc(String db_include_exp) { String db_start_fld_Timefunc = " CONVERT(varchar(100), db_start_fld, 8)"; String db_end_fld_Timefunc = " CONVERT(varchar(100), db_end_fld, 8)"; return db_include_exp.replaceAll("db_start_fld", db_start_fld_Timefunc).replaceAll("db_end_fld", db_end_fld_Timefunc); } }; List<String> finalList= new ArrayList<String>() {{ for (String dateExp : dateExpList) { for (String timeExp : timeExpList) { this.add(dateExp+" and "+timeExp.replaceAll("db_start_fld", db_start_fld).replaceAll("db_end_fld", db_end_fld)); } } }}; String sql=new ArrayList(){ private String exec( ) { String exp_final=" 1=1 "; for (String exp : finalList) { //if(exp_final.length()==0) exp_final=exp_final + " or ( "+exp +" ) "; } return exp_final;
}}.exec( ); System.out.println(sql);
}
4. 最终生成的sql
SELECT * FROM tab WHERE 1=1 OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end AND CONVERT(varchar(100), start_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8)<=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end AND CONVERT(varchar(100), start_time, 8)<=ui_start AND CONVERT(varchar(100), end_time, 8)>=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end AND CONVERT(varchar(100), end_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8) <=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end AND CONVERT(varchar(100), start_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8)<=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)<=ui_start AND CONVERT(varchar(100), db_end_fld, 23)>=ui_end AND CONVERT(varchar(100), start_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8)<=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)<=ui_start AND CONVERT(varchar(100), db_end_fld, 23)>=ui_end AND CONVERT(varchar(100), start_time, 8)<=ui_start AND CONVERT(varchar(100), end_time, 8)>=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)<=ui_start AND CONVERT(varchar(100), db_end_fld, 23)>=ui_end AND CONVERT(varchar(100), end_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8) <=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)<=ui_start AND CONVERT(varchar(100), db_end_fld, 23)>=ui_end AND CONVERT(varchar(100), start_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8)<=ui_end) OR (CONVERT(varchar(100), db_end_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23) <=ui_end AND CONVERT(varchar(100), start_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8)<=ui_end) OR (CONVERT(varchar(100), db_end_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23) <=ui_end AND CONVERT(varchar(100), start_time, 8)<=ui_start AND CONVERT(varchar(100), end_time, 8)>=ui_end) OR (CONVERT(varchar(100), db_end_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23) <=ui_end AND CONVERT(varchar(100), end_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8) <=ui_end) OR (CONVERT(varchar(100), db_end_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23) <=ui_end AND CONVERT(varchar(100), start_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8)<=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end AND CONVERT(varchar(100), start_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8)<=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end AND CONVERT(varchar(100), start_time, 8)<=ui_start AND CONVERT(varchar(100), end_time, 8)>=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end AND CONVERT(varchar(100), end_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8) <=ui_end) OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end AND CONVERT(varchar(100), start_time, 8)>=ui_start AND CONVERT(varchar(100), end_time, 8)<=ui_end)
5. 参考java 时间段重合时间差 - 天才少年程序员-李荣盛专栏 - 博客频道 - CSDN.NET JAVA中如何判断两个时间段是否有交集-CSDN论坛-CSDN.NET-中国最大的IT技术社区 比较两个日期间隔是否有碰撞的工具类,判断两个时间区间是否有交集(单位天) Java - 和申的日志 - 网易博客 |
2022-08-30
2022-08-17
2022-11-06
2022-08-18
2022-07-18
请发表评论