当前位置:Gxlcms > mysql > atitit.判断时间重叠方法总结javac++c#.netjsphp

atitit.判断时间重叠方法总结javac++c#.netjsphp

时间:2021-07-01 10:21:17 帮助过:8人阅读

atitit.判断时间重叠方法总结 java c++ c#.net js php 1. 判断时间重叠具体流程思路 1 2. 重叠算法 实际上就是日期集合跟个时间集合的的交集(乘法算法) 1 3. 代码--- 1 4. 最终生成的sql 3 5. 参考 5 1. 判断时间重叠具体流程思路 先判断日期重叠,在判断时

atitit.判断时间重叠方法总结 java c++ c#.net js php

1. 判断时间重叠具体流程思路 1

2. 重叠算法 实际上就是日期集合跟个时间集合的的交集(乘法算法) 1

3. 代码--- 1

4. 最终生成的sql 3

5. 参考 5

1. 判断时间重叠具体流程思路

先判断日期重叠,在判断时间区段重叠。

每个区段都有内包含,外包含,左包含,右包括...所以,or表达式需要4*4=16个..每个or 表达式包括4个and表达式( 两个日期表达式,两个时间范围表达式)

最终的最终的的表达式需要16*4=64个,,比较长的了..

2. 重叠算法 实际上就是日期集合跟个时间集合的的交集(乘法算法)

在程序设计里有。。。。。

回答

那实际上就是交、并、差。
所谓加,实际上就是求两个集合的并集
减,就是求两个集合的差集
乘,就是求两个集合的并集
它们都是从逻辑的运算推出来的

另外更正一下:乘是求两个集合的交集。

3. 代码---

  1. private void ini() {
  2. String db_start_fld = "start_time";
  3. String db_end_fld = "end_time";
  4. String db_include_exp = "db_start_fld>=ui_start and db_end_fld<=ui_end ";
  5. String db_outclude_exp = "db_start_fld<=ui_start and db_end_fld>=ui_end ";
  6. String db_leftinclude_exp = "db_end_fld>=ui_start and db_end_fld <=ui_end ";
  7. String db_ritinclude_exp = "db_start_fld>=ui_start and db_end_fld<=ui_end ";
  8. List<string> dateExpList = new ArrayList<string>() {
  9. {
  10. add(expandFunc(db_include_exp));
  11. add(expandFunc(db_outclude_exp));
  12. add(expandFunc(db_leftinclude_exp));
  13. add(expandFunc(db_ritinclude_exp));
  14. }
  15. private String expandFunc(String db_include_exp) {
  16. String db_start_fld_Datefunc = " CONVERT(varchar(100), db_start_fld, 23)";
  17. String db_end_fld_Datefunc = " CONVERT(varchar(100), db_end_fld, 23)";
  18. return db_include_exp.replaceAll("db_start_fld",
  19. db_start_fld_Datefunc).replaceAll("db_end_fld",
  20. db_end_fld_Datefunc);
  21. }
  22. };
  23. List<string> timeExpList = new ArrayList<string>() {
  24. {
  25. add(expandFunc(db_include_exp));
  26. add(expandFunc(db_outclude_exp));
  27. add(expandFunc(db_leftinclude_exp));
  28. add(expandFunc(db_ritinclude_exp));
  29. }
  30. private String expandFunc(String db_include_exp) {
  31. String db_start_fld_Timefunc = " CONVERT(varchar(100), db_start_fld, 8)";
  32. String db_end_fld_Timefunc = " CONVERT(varchar(100), db_end_fld, 8)";
  33. return db_include_exp.replaceAll("db_start_fld",
  34. db_start_fld_Timefunc).replaceAll("db_end_fld",
  35. db_end_fld_Timefunc);
  36. }
  37. };
  38. List<string> finalList= new ArrayList<string>() {{
  39. for (String dateExp : dateExpList) {
  40. for (String timeExp : timeExpList) {
  41. this.add(dateExp+" and "+timeExp.replaceAll("db_start_fld", db_start_fld).replaceAll("db_end_fld", db_end_fld));
  42. }
  43. }
  44. }};
  45. String sql=new ArrayList(){
  46. private String exec( ) {
  47. String exp_final=" 1=1 ";
  48. for (String exp : finalList) {
  49. //if(exp_final.length()==0)
  50. exp_final=exp_final + " or ( "+exp +" ) ";
  51. }
  52. return exp_final;
  53. }}.exec( );
  54. System.out.println(sql);
  55. }
  56. </string></string></string></string></string></string>

4. 最终生成的sql

  1. SELECT *
  2. FROM tab
  3. WHERE 1=1
  4. OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start
  5. AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end
  6. AND CONVERT(varchar(100), start_time, 8)>=ui_start
  7. AND CONVERT(varchar(100), end_time, 8)<=ui_end)
  8. OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start
  9. AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end
  10. AND CONVERT(varchar(100), start_time, 8)<=ui_start
  11. AND CONVERT(varchar(100), end_time, 8)>=ui_end)
  12. OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start
  13. AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end
  14. AND CONVERT(varchar(100), end_time, 8)>=ui_start
  15. AND CONVERT(varchar(100), end_time, 8) <=ui_end)
  16. OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start
  17. AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end
  18. AND CONVERT(varchar(100), start_time, 8)>=ui_start
  19. AND CONVERT(varchar(100), end_time, 8)<=ui_end)
  20. OR (CONVERT(varchar(100), db_start_fld, 23)<=ui_start
  21. AND CONVERT(varchar(100), db_end_fld, 23)>=ui_end
  22. AND CONVERT(varchar(100), start_time, 8)>=ui_start
  23. AND CONVERT(varchar(100), end_time, 8)<=ui_end)
  24. OR (CONVERT(varchar(100), db_start_fld, 23)<=ui_start
  25. AND CONVERT(varchar(100), db_end_fld, 23)>=ui_end
  26. AND CONVERT(varchar(100), start_time, 8)<=ui_start
  27. AND CONVERT(varchar(100), end_time, 8)>=ui_end)
  28. OR (CONVERT(varchar(100), db_start_fld, 23)<=ui_start
  29. AND CONVERT(varchar(100), db_end_fld, 23)>=ui_end
  30. AND CONVERT(varchar(100), end_time, 8)>=ui_start
  31. AND CONVERT(varchar(100), end_time, 8) <=ui_end)
  32. OR (CONVERT(varchar(100), db_start_fld, 23)<=ui_start
  33. AND CONVERT(varchar(100), db_end_fld, 23)>=ui_end
  34. AND CONVERT(varchar(100), start_time, 8)>=ui_start
  35. AND CONVERT(varchar(100), end_time, 8)<=ui_end)
  36. OR (CONVERT(varchar(100), db_end_fld, 23)>=ui_start
  37. AND CONVERT(varchar(100), db_end_fld, 23) <=ui_end
  38. AND CONVERT(varchar(100), start_time, 8)>=ui_start
  39. AND CONVERT(varchar(100), end_time, 8)<=ui_end)
  40. OR (CONVERT(varchar(100), db_end_fld, 23)>=ui_start
  41. AND CONVERT(varchar(100), db_end_fld, 23) <=ui_end
  42. AND CONVERT(varchar(100), start_time, 8)<=ui_start
  43. AND CONVERT(varchar(100), end_time, 8)>=ui_end)
  44. OR (CONVERT(varchar(100), db_end_fld, 23)>=ui_start
  45. AND CONVERT(varchar(100), db_end_fld, 23) <=ui_end
  46. AND CONVERT(varchar(100), end_time, 8)>=ui_start
  47. AND CONVERT(varchar(100), end_time, 8) <=ui_end)
  48. OR (CONVERT(varchar(100), db_end_fld, 23)>=ui_start
  49. AND CONVERT(varchar(100), db_end_fld, 23) <=ui_end
  50. AND CONVERT(varchar(100), start_time, 8)>=ui_start
  51. AND CONVERT(varchar(100), end_time, 8)<=ui_end)
  52. OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start
  53. AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end
  54. AND CONVERT(varchar(100), start_time, 8)>=ui_start
  55. AND CONVERT(varchar(100), end_time, 8)<=ui_end)
  56. OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start
  57. AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end
  58. AND CONVERT(varchar(100), start_time, 8)<=ui_start
  59. AND CONVERT(varchar(100), end_time, 8)>=ui_end)
  60. OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start
  61. AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end
  62. AND CONVERT(varchar(100), end_time, 8)>=ui_start
  63. AND CONVERT(varchar(100), end_time, 8) <=ui_end)
  64. OR (CONVERT(varchar(100), db_start_fld, 23)>=ui_start
  65. AND CONVERT(varchar(100), db_end_fld, 23)<=ui_end
  66. AND CONVERT(varchar(100), start_time, 8)>=ui_start
  67. AND CONVERT(varchar(100), end_time, 8)<=ui_end)

人气教程排行