这样的事情怎么样。我希望可以在没有子查询的情况下将其重写，但是我必须要动脑子…（为了适应变化的要求，对数据集和查询进行了修改）
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 |
+---------+------------+------+
602392714
清零编程群