mysql 连续n个_MySQL在正常表格中连续N天计数

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

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.

A sample table structure as follows:

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;

Sample inserts

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)

Desired output:

*+---------+-----------------+

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

+---------+------------+------+