MySQL在正常表格中连续N天计数

问题描述

我要计算特定用户在给定日期和之前的连续N天会议。

I want to count N consecutive days that a specific user has meetings, on a given date and before it.

例如：计算a ID为1的用户的时间为2013年1月16日。

For example: count the consecutive meeting days that a user with id 1 has at 16 January 2013.

我找到了一些很好的答案和，但是表的格式不像上面的示例那样正常，因此我无法弄清楚该如何实现它。

I found some good answers here and here but the tables are not in normal form like my sample above and i cannot figure out how to implement it for my occasion.

示例表结构如下：

CREATE TABLE IF NOT EXISTS `meetings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `meetings_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `meeting_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `meeting_id` (`meeting_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Constraints for table `meetings_users`
--
ALTER TABLE `meetings_users`
  ADD CONSTRAINT `meetings_users_ibfk_2` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `meetings_users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

样本插入

INSERT INTO  `users` ( `id` ) VALUES (1)

INSERT INTO `meetings` ( `id`, `time` ) VALUES 
(1, '2013-01-14 10:00:00'), 
(2, '2013-01-15 10:00:00'), 
(3, '2013-01-16 10:00:00')


INSERT INTO `meetings_users` ( `id`, `meeting_id`, `user_id` ) VALUES 
(1, 1, 1), 
(2, 2, 1), 
(3, 3, 1)

所需输出：

*+---------+-----------------+
| user_id | consecutive_days |
+---------+------------------+
|       1 | 3                |
+---------+------------------+

推荐答案

这样的事情怎么样。我希望它可以在没有子查询的情况下进行重写，但是我必须要花点时间...（对数据集和查询进行了修改，以适应不断变化的需求）

How about something like this. I expect it can be re-written without the subqueries but I must be having a bit of a brain freeze... (data set and query amended to suit shifting requirements)

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-14 12: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),
(1, 8);

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 |
 +---------+------------+------+ 

这篇关于MySQL在正常表格中连续N天计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！