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