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

作者: jiezzy | 来源:发表于2019-12-11 09:51 被阅读0次

创建透视表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

转https://my.oschina.net/u/3238650/blog/3019543
https://my.oschina.net/u/3238650/blog/3020350

mysql 分组行号查询

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=customerNumber as CustomerNumber,
    paymentDate,
    amount
FROM
    payments,(SELECT @customer_no:=0,@row_number:=0) as t
ORDER BY customerNumber;


---
SET @row_number = 0;
 
SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees
LIMIT 5;



---
SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees,(SELECT @row_number:=0) AS t
LIMIT 5;

SQL查询存在一个表而不在另一个表中的数据

方法1
使用 not in ,容易理解,效率低

select distinct A.ID from  A where A.ID not in (select ID from B)
方法2
使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录

select A.ID from A left join B on A.ID=B.ID where B.ID is null
方法3
逻辑相对复杂,但是速度最快

select * from  B where (select count(1) as num from A where A.ID = B.ID) = 0

SELECT a.`name`,b.week_day,b.good_id from t_mall_activity a
INNER JOIN t_mall_activity_second_kill b on a.id = b.activity_id
where now() >= a.start_date  and now() < date_add(a.stop_date, interval 1 day)
and a.`enable`=1 and b.`enable` = 1 and (b.week_day = DATE_FORMAT(now(), '%w') or (b.week_day = 7 and DATE_FORMAT(now(), '%w') = 0))

SELECT sum(case when d.week_day = (case DATE_FORMAT( DATE_ADD(STR_TO_DATE( t.start_date, '%Y-%m-%d' ),INTERVAL a.id - 1 DAY ),'%w' )
        when 0 then 7 
        else DATE_FORMAT( DATE_ADD(STR_TO_DATE( t.start_date, '%Y-%m-%d' ),INTERVAL a.id - 1 DAY ),'%w' ) 
    end) then 1 else 0 end) as days
 from t_380 a
INNER JOIN t_mall_activity t on TRUE
INNER JOIN t_mall_activity_second_kill d on d.activity_id = t.id
where a.id <= DATEDIFF( t.stop_date , t.start_date ) +1
and d.id = 1




SELECT * from
(
    SELECT * from
    (
    SELECT a.`name`,b.id, b.good_id,b.week_day,
        b.title,b.limit_count,b.stock_count,
        subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7) date, b.start_time,b.kill_time,
        str_to_date(CONCAT(subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7),b.start_time),'%Y-%m-%d%H:%i:%s') begin_time,
        date_add(str_to_date(CONCAT(subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7),b.start_time),'%Y-%m-%d%H:%i:%s'),interval b.kill_time minute) end_time,
        b.price,b.member_price from t_mall_activity a
        INNER JOIN t_mall_activity_second_kill b on a.id = b.activity_id
        where now() >= a.start_date  and now() < date_add(a.stop_date, interval 1 day)
        and a.`enable`=1 and b.`enable` = 1
        and subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7) >= a.start_date
        and subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7) < date_add(a.stop_date, interval 1 day)
    UNION all

    SELECT a.`name`,b.id, b.good_id,b.week_day,
        b.title,b.limit_count,b.stock_count,
        subdate(curdate(),date_format(curdate(),'%w') - b.week_day) date, b.start_time,b.kill_time,
        str_to_date(CONCAT(subdate(curdate(),date_format(curdate(),'%w') - b.week_day),b.start_time),'%Y-%m-%d%H:%i:%s') begin_time,
        date_add(str_to_date(CONCAT(subdate(curdate(),date_format(curdate(),'%w') - b.week_day),b.start_time),'%Y-%m-%d%H:%i:%s'),interval b.kill_time minute) end_time,
        b.price,b.member_price from t_mall_activity a
        INNER JOIN t_mall_activity_second_kill b on a.id = b.activity_id
        where now() >= a.start_date  and now() < date_add(a.stop_date, interval 1 day)
        and a.`enable`=1 and b.`enable` = 1
        and subdate(curdate(),date_format(curdate(),'%w') - b.week_day) >= a.start_date
        and subdate(curdate(),date_format(curdate(),'%w') - b.week_day) < date_add(a.stop_date, interval 1 day)
        ) t where t.date < DATE_FORMAT(NOW(), '%Y-%m-%d') ORDER BY date desc LIMIT 1
)t1
UNION
SELECT * from
(
SELECT a.`name`,b.id, b.good_id,b.week_day,
        b.title,b.limit_count,b.stock_count,
        subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7) date, b.start_time,b.kill_time,
        str_to_date(CONCAT(subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7),b.start_time),'%Y-%m-%d%H:%i:%s') begin_time,
        date_add(str_to_date(CONCAT(subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7),b.start_time),'%Y-%m-%d%H:%i:%s'),interval b.kill_time minute) end_time,
        b.price,b.member_price from t_mall_activity a
        INNER JOIN t_mall_activity_second_kill b on a.id = b.activity_id
        where now() >= a.start_date  and now() < date_add(a.stop_date, interval 1 day)
        and a.`enable`=1 and b.`enable` = 1
        and subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7) >= a.start_date
        and subdate(curdate(),date_format(curdate(),'%w') - b.week_day+7) < date_add(a.stop_date, interval 1 day)
    UNION all
    SELECT a.`name`,b.id, b.good_id,b.week_day,
        b.title,b.limit_count,b.stock_count,
        subdate(curdate(),date_format(curdate(),'%w') - b.week_day) date, b.start_time,b.kill_time,
        str_to_date(CONCAT(subdate(curdate(),date_format(curdate(),'%w') - b.week_day),b.start_time),'%Y-%m-%d%H:%i:%s') begin_time,
        date_add(str_to_date(CONCAT(subdate(curdate(),date_format(curdate(),'%w') - b.week_day),b.start_time),'%Y-%m-%d%H:%i:%s'),interval b.kill_time minute) end_time,
        b.price,b.member_price from t_mall_activity a
        INNER JOIN t_mall_activity_second_kill b on a.id = b.activity_id
        where now() >= a.start_date  and now() < date_add(a.stop_date, interval 1 day)
        and a.`enable`=1 and b.`enable` = 1
        and subdate(curdate(),date_format(curdate(),'%w') - b.week_day) >= a.start_date
        and subdate(curdate(),date_format(curdate(),'%w') - b.week_day) < date_add(a.stop_date, interval 1 day)
) t2 where t2.date >= DATE_FORMAT(NOW(), '%Y-%m-%d') ;

网友评论

本文标题：mysql 计算两个日期之间的工作日天数

本文链接：https://www.haomeiwen.com/subject/rffogctx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

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

mysql 分组行号查询

SQL查询存在一个表而不在另一个表中的数据

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读