MySQL在datediff中跳过周末

Question

在之前关于"在正常表格中计算连续N天"的问题中，我得到了下面这个答案，工作得很好。我意识到我需要跳过周末的计算，例如:如果用户在周五和下周一都有会议，那么这应该算作连续两天，而不是因为周末而中断。

下面的代码解释了我现在拥有的东西。

DROP TABLE IF EXISTS meetings;
CREATE TABLE IF NOT EXISTS meetings 
( meeting_id int(10) unsigned NOT NULL AUTO_INCREMENT
, meeting_time datetime NOT NULL
, PRIMARY KEY (meeting_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS meetings_users;
CREATE TABLE IF NOT EXISTS meetings_users 
( user_id int(10) unsigned NOT NULL
, meeting_id int(10) unsigned NOT NULL
, PRIMARY KEY (meeting_id,user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users 
( user_id int(10) unsigned NOT NULL AUTO_INCREMENT
, PRIMARY KEY (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
INSERT INTO  users ( user_id ) VALUES (1),(2),(3),(4);
INSERT INTO meetings ( meeting_id, meeting_time ) VALUES 
(1, '2013-01-14 10:00:00'), 
(2, '2013-01-15 10:00:00'), 
(3, '2013-01-16 10:00:00'),
(4, '2013-01-17 10:00:00'),
(5, '2013-01-18 10:00:00'),
(6, '2013-01-19 10:00:00'),
(7, '2013-01-20 10:00:00'),
(8, '2013-01-11 10:00:00');

INSERT INTO meetings_users (meeting_id, user_id ) VALUES 
(1, 1), 
(2, 1),
(2, 3),
(3, 1),
(3, 3),
(4, 2),
(4, 3), 
(5, 2), 
(6, 1),
(8, 1);
SET @dt = '2013-01-15';
SELECT user_id
     , start
     , DATEDIFF(@dt,start)+1 cons
  FROM
     (
       SELECT a.user_id
            , a.meeting_date Start
            , MIN(c.meeting_date) End
        , DATEDIFF(MIN(c.meeting_date),a.meeting_date)  + 1 diff
         FROM (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) a
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) b
       ON b.user_id = a.user_id
          AND a.meeting_date = b.meeting_date + INTERVAL 1 DAY
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) c
       ON c.user_id = a.user_id
          AND a.meeting_date <= c.meeting_date
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) d
           ON d.user_id = a.user_id
          AND c.meeting_date = d.meeting_date - INTERVAL 1 DAY
        WHERE b.meeting_date IS NULL
      AND c.meeting_date IS NOT NULL
          AND d.meeting_date IS NULL
        GROUP
       BY a.user_id
        , a.meeting_date
     ) x
 WHERE @dt BETWEEN start AND end;
 +---------+------------+------+
 | user_id | start      | cons |
 +---------+------------+------+
 |       1 | 2013-01-14 |    2 |
 |       3 | 2013-01-15 |    1 |
 +---------+------------+------+ 

我尝试创建自己的函数来取代原始的DATEDIFF，以便根据这个答案跳过周末(我将其命名为CustomDateDiff)并且工作得很好，但我认为问题仍然存在于a.meeting_date = b.meeting_date + INTERVAL 1 DAY，我应该跳过INTERVAL，我猜。

Answer 1

好的，从这里抓取一些代码，我已经修改了我之前的响应因子@dt…

@dt = '2013-01-15';
SELECT *
     , @dt target
     , 5 * (DATEDIFF(@dt, start) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(start) + WEEKDAY(@dt) + 1, 1)+1 cons
FROM (
    SELECT x.user_id
         , MIN(x.start) start
         , COALESCE(y.end,x.end) end
         , MAX(COALESCE(y.diff,0)+x.diff) ttl
      FROM
         (
         SELECT a.user_id
                , a.meeting_date Start
                , MIN(c.meeting_date) End
            , DATEDIFF(MIN(c.meeting_date),a.meeting_date)  + 1 diff
             FROM (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) a
             LEFT
             JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) b
           ON b.user_id = a.user_id
              AND a.meeting_date = b.meeting_date + INTERVAL 1 DAY
             LEFT
             JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) c
           ON c.user_id = a.user_id
              AND a.meeting_date <= c.meeting_date
             LEFT
             JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) d
               ON d.user_id = a.user_id
              AND c.meeting_date = d.meeting_date - INTERVAL 1 DAY
            WHERE b.meeting_date IS NULL
          AND c.meeting_date IS NOT NULL
              AND d.meeting_date IS NULL
            GROUP
           BY a.user_id
            , a.meeting_date
    )x
    LEFT JOIN
       (
         SELECT a.user_id
                , a.meeting_date Start
                , MIN(c.meeting_date) End
            , DATEDIFF(MIN(c.meeting_date),a.meeting_date)  + 1 diff
             FROM (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) a
             LEFT
             JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) b
           ON b.user_id = a.user_id
              AND a.meeting_date = b.meeting_date + INTERVAL 1 DAY
             LEFT
             JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) c
           ON c.user_id = a.user_id
              AND a.meeting_date <= c.meeting_date
             LEFT
             JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) d
               ON d.user_id = a.user_id
              AND c.meeting_date = d.meeting_date - INTERVAL 1 DAY
            WHERE b.meeting_date IS NULL
          AND c.meeting_date IS NOT NULL
              AND d.meeting_date IS NULL
            GROUP
           BY a.user_id
            , a.meeting_date
    )y
    ON y.user_id = x.user_id
    AND y.start = x.end+INTERVAL 3 DAY AND WEEKDAY(x.end) = 4
    GROUP BY user_id,end
)j
WHERE @dt BETWEEN start AND end;