日期范围内的SQL查找计数
日期范围内的SQL查找计数
提问于 2020-10-26 10:13:33
我有一张桌子如下：
+--------+------------+------------+----------------+---------------+------------+
| UserID | CaseNumber | CourseName | CourseAttended | Training_Date | Created_On |
+--------+------------+------------+----------------+---------------+------------+
| 1 | 101 | X | T | 01/01/2020 | 01/12/2019 |
| 1 | 103 | X | T | 01/05/2020 | 01/12/2018 |
| 1 | 105 | X | T | 01/02/2020 | 03/03/2020 |
| 2 | 101 | X | T | 05/01/2020 | 03/01/2020 |
| 3 | 103 | X | T | 05/05/2020 | 01/01/2020 |
| 4 | 104 | X | T | 04/01/2020 | 01/08/2020 |
| 5 | 101 | X | T | 01/07/2020 | 01/02/2020 |
| 5 | 105 | X | T | 01/08/2020 | 01/12/2019 |
| 5 | 109 | X | T | 01/09/2020 | 01/10/2019 |我想要计算训练日期小于创建日期的CaseNumber计数，也要计算培训日期>创建日期的情况。用户有多个培训日期，仅考虑用户的最新培训日期。计数应仅针对已创建的病例+-3个月的培训日期。例如，如果创建日期为2018年，培训日期为2020年，则不应将其计算在内。这些病例只需要计算在+-3个月内的培训日期。
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-10-26 11:57:23
最后一次查询
根据您的要求，我逐步实现了以下要求：
SELECT count(1)
FROM
(SELECT d.userid,
d.caseNumber,
d.training_Date,
d.created_on,
period_diff(date_format(d.training_Date, '%Y%m'), date_format(d.created_on, '%Y%m')) AS monthsDiff
FROM DATA d
INNER JOIN
(SELECT userId,
max(training_date) AS lastTraining
FROM DATA
GROUP BY userid)ltu ON d.userid = ltu.userid
AND d.training_Date = ltu.lastTraining) d
WHERE (monthsDiff >=0
AND monthsDiff < 4)
AND (monthsDiff <0
AND monthsDiff > -4);使用视图的解决方案(包括假数据)
以下是数据：
create table data (userid int,
caseNumber int,
training_Date date,
created_On date);插入声明：
insert into data( userid, caseNumber, training_Date, created_on) values( 1 , 101 ,STR_TO_DATE('01-01-2020','%d-%c-%Y'),STR_TO_DATE('01-12-2019','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values( 1 , 103 ,STR_TO_DATE('01-05-2020','%d-%c-%Y'),STR_TO_DATE('01-12-2018','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values( 1 , 105 ,STR_TO_DATE('01-02-2020','%d-%c-%Y'),STR_TO_DATE('03-03-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values( 2 , 101 ,STR_TO_DATE('05-01-2020','%d-%c-%Y'),STR_TO_DATE('03-01-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values( 3 , 103 ,STR_TO_DATE('05-05-2020','%d-%c-%Y'),STR_TO_DATE('01-01-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values( 4 , 104 ,STR_TO_DATE('04-01-2020','%d-%c-%Y'),STR_TO_DATE('01-08-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values( 5 , 101 ,STR_TO_DATE('01-07-2020','%d-%c-%Y'),STR_TO_DATE('01-02-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values( 5 , 105 ,STR_TO_DATE('01-08-2020','%d-%c-%Y'),STR_TO_DATE('01-12-2019','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values( 5 , 109 ,STR_TO_DATE('01-09-2020','%d-%c-%Y'),STR_TO_DATE('01-10-2019','%d-%c-%Y'));只获得最后一次训练日期：
create View lastTrainingByUser as
select userId, max(training_date) as lastTraining from data group by userid;只得到最后一次训练日期的病例。还计算训练和创建之间的月差。
create view lastTrainingDatesByUser as
select d.userid, d.caseNumber, d.training_Date, d.created_on,
period_diff(date_format(d.training_Date, '%Y%m'), date_format(d.created_on, '%Y%m')) as monthsDiff
from data d inner join lastTrainingByUser ltu
on d.userid = ltu.userid AND d.training_Date = ltu.lastTraining;没有计数的查询
select d.userid, d.caseNumber, d.training_Date, d.created_on
,d.monthsDiff
from lastTrainingDatesByUser d
where
(monthsDiff >=0 AND monthsDiff < 4) AND
(monthsDiff <=0 AND monthsDiff > -4);具有计数的查询：
select count(1)
from lastTrainingDatesByUser d
where
(monthsDiff >=0 AND monthsDiff < 4) AND
(monthsDiff < 0 AND monthsDiff > -4);SQLFiddle：http://sqlfiddle.com/#!9/9184b0/7
Stack Overflow用户
发布于 2020-10-26 10:59:27
根据描述，这听起来像是一个窗口函数，用于获取最大的培训日期，然后进行一些聚合：
select sum(training_date < create_date) as num_before,
sum(training_date > create_date) as num_after
from (select t.*,
max(training_date) over (partition by user) as max_td
)
where training_date = max_training_date and
training_date >= create_date - interval 3 month and
training_date <= create_date + interval 3 month ;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64535121
复制相关文章
相似问题
腾讯云开发者
