Mysql 查询获取日期之间的可数匹配条目并加入它们以读取用户状态

您需要做的第一件事是像在selected_range子查询中那样连接输入日期.

在我的理解中，以下查询是正确的，因为如果一个记录有valid_till = null，这意味着它在NOW之前仍然有效，对吗？在你的例子中，2022-07-06和2022-07-07是匹配的，因为记录D在2022-07-03到NOW之间有效

############## ONLY MATCHES
SELECT selected_range.date,
       COUNT(records.id) matches
FROM (        SELECT '2022-06-30' date
    UNION ALL SELECT '2022-07-01'
    UNION ALL SELECT '2022-07-02'
    UNION ALL SELECT '2022-07-03'
    UNION ALL SELECT '2022-07-04'
    UNION ALL SELECT '2022-07-05'
    UNION ALL SELECT '2022-07-06'
    UNION ALL SELECT '2022-07-07') selected_range
LEFT JOIN records ON selected_range.date BETWEEN records.valid_from 
                 AND COALESCE (records.valid_till, CAST(NOW() AS DATE))
GROUP BY selected_range.date        

############# MATCHES READ BY USER
SELECT selected_range.date,
       COUNT(records.id) matches,
       COUNT(viewstates.USER) already_read_by_me
FROM (        SELECT '2022-06-30' date
    UNION ALL SELECT '2022-07-01'
    UNION ALL SELECT '2022-07-02'
    UNION ALL SELECT '2022-07-03'
    UNION ALL SELECT '2022-07-04'
    UNION ALL SELECT '2022-07-05'
    UNION ALL SELECT '2022-07-06'
    UNION ALL SELECT '2022-07-07') selected_range
LEFT JOIN records ON selected_range.date BETWEEN records.valid_from 
                 AND COALESCE (records.valid_till, CAST(NOW() AS DATE))
LEFT JOIN viewstates ON viewstates.record = records.id 
                    AND viewstates.USER = 'X' 
GROUP BY selected_range.date

但是你的预期结果是可能的，然而我能够注意到的逻辑是，对于valid_till=null的记录是匹配的，必须有另一个有valid_till NOT null的记录.例如，对于2022-07-05，D记录匹配，因为记录A和B也匹配.

但对于2022-07-06和2022-07-07，没有考虑记录D，我理解这是因为没有其他匹配的记录.

所以我实现了这个逻辑:

######## ONLY MATCHES
SELECT selected_range.date,
       COUNT(records.id) matches
FROM (        SELECT '2022-06-30' date
    UNION ALL SELECT '2022-07-01'
    UNION ALL SELECT '2022-07-02'
    UNION ALL SELECT '2022-07-03'
    UNION ALL SELECT '2022-07-04'
    UNION ALL SELECT '2022-07-05'
    UNION ALL SELECT '2022-07-06'
    UNION ALL SELECT '2022-07-07') selected_range
LEFT JOIN records ON (selected_range.date BETWEEN records.valid_from 
                 AND COALESCE (records.valid_till, records.valid_from))
                 OR (EXISTS (SELECT 1 
                             FROM records records_inner
                             WHERE (selected_range.date BETWEEN records_inner.valid_from 
                                                        AND records_inner.valid_till)
                      AND selected_range.date >= records.valid_from
                      AND records.valid_till IS NULL
                 ))
GROUP BY selected_range.date 
                

######## MATCHES READ BY THE USER
SELECT selected_range.date,
       COUNT(records.id) matches,
       COUNT(viewstates.user) already_read_by_me
FROM (        SELECT '2022-06-30' date
    UNION ALL SELECT '2022-07-01'
    UNION ALL SELECT '2022-07-02'
    UNION ALL SELECT '2022-07-03'
    UNION ALL SELECT '2022-07-04'
    UNION ALL SELECT '2022-07-05'
    UNION ALL SELECT '2022-07-06'
    UNION ALL SELECT '2022-07-07') selected_range
LEFT JOIN records ON (selected_range.date BETWEEN records.valid_from 
                 AND COALESCE (records.valid_till, records.valid_from))
                 OR (EXISTS (SELECT 1 
                             FROM records records_inner
                             WHERE (selected_range.date BETWEEN records_inner.valid_from 
                                                        AND records_inner.valid_till)
                      AND selected_range.date >= records.valid_from
                      AND records.valid_till IS NULL
                 ))
LEFT JOIN viewstates ON viewstates.record = records.id 
                    AND viewstates.USER = 'X'
GROUP BY selected_range.date 

如果你觉得我的答案有用，请标记为接受并投票.