mysql 计算两个日期之间的工作日天数

时间:2023-03-09 16:51:40
mysql 计算两个日期之间的工作日天数

  创建透视表t500
  
  建表
  
  CREATE TABLE `t500` (
  
  `id` int(11) NOT NULL AUTO_INCREMENT,
  
  PRIMARY KEY (`id`)
  
  ) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=latin1;
  
  插入500条数据
  
  CREATE DEFINER=`json`@`%` PROCEDURE `i500`()
  
  BEGIN
  
  DECLARE a INT default 1;
  
  while a<=500 do
  
  insert into t500 (id) value(a);
  
  set a=a+1;
  
  end while;
  
  end
  
  2.1
  
  select t500.id from t500 where t500.id <=DATEDIFF("2019-02-01","2019-01-01")+1
  
  2.2
  
  SELECT
  
  DATE_ADD(
  
  STR_TO_DATE( "2019-01-01", '%Y-%m-%d' ),
  
  INTERVAL t500.id - 1 DAY
  
  )
  
  FROM
  
  t500
  
  WHERE
  
  t500.id <= DATEDIFF( "2019-02-01", "2019-01-01" ) +1
  
  2.3
  
  SELECT
  
  DATE_FORMAT(
  
  DATE_ADD(
  
  STR_TO_DATE( "2019-01-01", '%Y-%m-%d' ),
  
  INTERVAL t500.id - 1 DAY
  
  ),
  
  '%a'
  
  )
  
  FROM
  
  t500
  
  WHERE
  
  t500.id <= DATEDIFF( "2019-02-01", "2019-01-01" ) +1
  
  2.4
  
  SELECT
  
  CASE
  
  WHEN
  
  DATE_FORMAT(
  
  DATE_ADD(
  
  STR_TO_DATE( "2019-01-01", '%Y-%m-%d' ),
  
  INTERVAL t500.id - 1 DAY
  
  ),
  
  '%a'
  
  ) IN ( 'Sat', 'Sun' ) THEN
  
  0 ELSE 1 end
  
  FROM
  
  t500
  
  WHERE
  
  t500.id <= DATEDIFF( "2019-02-01", "2019-01-01" ) +1
  
  2.5 最终SQL
  
  select sum(CASE
  
  WHEN
  
  DATE_FORMAT(
  
  DATE_ADD(
  
  STR_TO_DATE( "2019-01-01", '%Y-%m-%d' ),
  
  INTERVAL t500.id - 1 DAY
  
  ),
  
  '%a'
  
  ) IN ( 'Sat', 'Sun' ) THEN
  
  0 ELSE 1 end ) as days from
  
  t500
  
  WHERE
  
  t500.id <= DATEDIFF( "2019-02-01", "2019-01-01" ) +1
  
  @SpringBootApplication
  
  @ComponentScan(value =www.dfgjpt.com "indi.viyoung.viboot.*")
  
  @MapperScan(value = "indi.viyoung.viboot.swagger2.mapper")
  
  @EnableSwagger2
  
  @EnableSwaggerBootstrapUI
  
  public class ViBootSwaggerApplication {
  
  public static void main(www.fengshen157.com String[www.mhylpt.com] args) {
  
  SpringApplication.run(ViBootSwaggerApplication.class, args);
  
  }
  
  }
  
  第四步:通过注解来完成API文档
  
  1. @Api
  
  注解名称 注解属性 作用域 属性作用
  
  @Api tags 类 说明该类的作用
  
  value 类 说明该类的作用
  
  举个