mysql - 如何消除 DATEDIFF 中的重复子查询/表达式？

Question

我的数据库是登录日期的样本。我想计算不同行上连续登录日期之间的日期差异。例如:

user_id     login_date  
1   2012-05-22
1   2012-05-25
/* difference is 3 days */

我能够找出两个查询来执行此计算，但在这两个查询中我需要复制一个子查询/表达式以获得我想要的结果。

我尝试在 datediff 中使用“nextdate”，但出现错误:

#1054 - Unknown column 'nextdate' in 'field list' 

有没有办法消除重复？如果可以产生所需的结果，则可以接受全新的查询。

示例数据库

CREATE TABLE IF NOT EXISTS `tbl` (
  `user_id` int(11) DEFAULT NULL,
  `login_date` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl` (`user_id`, `login_date`) VALUES
(1, '2012-04-01'),
(1, '2012-04-25'),
(1, '2012-05-03'),
(1, '2012-05-04'),
(1, '2012-05-05'),
(1, '2012-05-06'),
(1, '2012-05-07'),
(1, '2012-05-09'),
(1, '2012-05-10'),
(1, '2012-05-11'),
(1, '2012-05-12'),
(1, '2012-05-16'),
(1, '2012-05-19'),
(1, '2012-05-20'),
(1, '2012-05-21'),
(1, '2012-05-22'),
(1, '2012-05-25'),
(2, '2012-04-02'),
(2, '2012-04-03'),
(2, '2012-04-04'),
(2, '2012-05-04'),
(2, '2012-05-06'),
(2, '2012-05-08'),
(2, '2012-05-09'),
(2, '2012-05-11'),
(2, '2012-05-17'),
(2, '2012-05-18'),
(2, '2012-05-19'),
(2, '2012-05-20'),
(2, '2012-05-21'),
(2, '2012-05-22'),
(2, '2012-05-25'),
(2, '2012-05-26'),
(2, '2012-05-27'),
(2, '2012-05-28'),
(2, '2012-05-29'),
(2, '2012-05-30'),
(2, '2012-05-31'),
(2, '2012-06-01'),
(2, '2012-06-02');

工作查询#1

SELECT 
a.`user_id`,
a.`login_date`,
(SELECT 
    MIN(b.`login_date`) 
    FROM `tbl` b 
    WHERE a.`login_date` < b.`login_date` 
    AND a.`user_id` = b.`user_id`
) AS `nextdate`,
DATEDIFF((SELECT 
    MIN(b.`login_date`) 
    FROM `tbl` b 
    WHERE a.`login_date` < b.`login_date` 
    AND a.`user_id` = b.`user_id`
), a.`login_date`) AS `timespan`
FROM `tbl` a 
WHERE 1=1
AND (a.`login_date` >= '2012-05-10' AND a.`login_date` <= '2012-05-25')
HAVING `nextdate` IS NOT NULL
ORDER BY a.`user_id` ASC, a.`login_date` ASC

工作查询 #2

SELECT 
a.`user_id`,
a.`login_date`,
MIN(b.`login_date`) AS `nextdate`,
DATEDIFF(MIN(b.`login_date`), a.`login_date`) AS `timespan`
FROM 
(
    SELECT 
    `user_id`, 
    `login_date`
    FROM `tbl`
) a 
JOIN 
(
    SELECT 
    `user_id`,
    `login_date`
    FROM `tbl`
) b 
ON a.`user_id` = b.`user_id`
AND a.`login_date` < b.`login_date` 
WHERE 1=1 
AND (a.`login_date` >= '2012-05-10' AND a.`login_date` <= '2012-05-25')
GROUP BY a.`user_id`,a.`login_date`

想要的结果

user_id     login_date  nextdate    timespan    
1   2012-05-10  2012-05-11  1
1   2012-05-11  2012-05-12  1
1   2012-05-12  2012-05-16  4
1   2012-05-16  2012-05-19  3
1   2012-05-19  2012-05-20  1
1   2012-05-20  2012-05-21  1
1   2012-05-21  2012-05-22  1
1   2012-05-22  2012-05-25  3
2   2012-05-11  2012-05-17  6
2   2012-05-17  2012-05-18  1
2   2012-05-18  2012-05-19  1
2   2012-05-19  2012-05-20  1
2   2012-05-20  2012-05-21  1
2   2012-05-21  2012-05-22  1
2   2012-05-22  2012-05-25  3
2   2012-05-25  2012-05-26  1

Answer 1

此查询与您的查询 #2 基本相同，只是使用了一个简单的自连接。使用 group by 和 min(login_date) 的自连接是最简单的，您可以将此查询简化为对 tbl a 进行范围扫描，然后对 tbl b 进行键查找。

select a.user_id, a.login_date, min(b.login_date), datediff(min(b.login_date), a.login_date)
from   tbl a
join   tbl b on a.user_id = b.user_id and a.login_date < b.login_date
where  (a.login_date >= '2012-05-10' AND a.login_date <= '2012-05-25')
group by a.user_id, a.login_date
order by a.user_id, a.login_date, b.login_date
;