日期部分的 MySQL 查询 [英] MySQL Query for Date Part

问题描述

我有两张桌子...

tblEvent
--------
id
date

tblEventRecurring
-----------------
event_id
date_part
end_date

关系是我可以在某一天(tblEvent 中的日期)或在与 tblEventRecurring 表相关的时间间隔(日、周、月、年)中重复发生事件.

The relationship is I can have an event that is either on one day (date in tblEvent) or recurs on an interval (Day, Week, Month, Year) which ties in the tblEventRecurring table.

例如，如果我将 2 月 10 日的活动重复到 2 月 28 日，我会...

For example, if I put in an event for Feb. 10 recurring until Feb. 28 I'd have...

tblEvent
--------
id = 1
date = 2012-02-10

tblEventRecurring
-----------------
event_id = 1
date_part = D
end_date = 2012-02-28

构建查询以获取满足以下条件的所有日期的最佳方法是什么...

what is the best way structure a query to get all dates where the following conditions are met...

• tblEvent.date = 今天
• 如果 tblEventRecurring.date_part = D 并且今天 <结束日期
• 如果 tblEventRecurring.date_part = W 并且今天与 tblEvent.date 是同一天(周日至周六)并且 <结束日期
• 如果 tblEventRecurring.date_part = M 并且今天与 tblEvent.date 是同一天，并且
• 结束日期等

有比另一种更有效的解决方案吗?这是使用 IF 或 CASE 或一堆 AND/OR 语句的好时机吗?

Is there one solution more efficient than another? Is this a good time to use IF or CASE or a bunch of AND/OR statements?

感谢您的指导.D.

由于我因为不研究而被否决，所以我想我会发布我的最终查询...我正在为一些 AND/OR 和括号苦苦挣扎.我做了研究，正在阅读我从未使用过的 CASE 的更多内容，所以我希望有人可以使用它来指导我的查询.

Since I was down voted for not researching, I thought I'd post my final query... I was struggling with some of the AND/OR and parentheses. I DID research and was reading more on CASE which I've never used so I hoped someone could possibly guide my query using it.

# where the current date is 2012-02-12
SELECT e.record_id, e.event_date, 
       DATE_FORMAT(e.event_time, '%l:%i %p') AS event_time, 
       e.category, pm.person_id, pm.status, pm.active
FROM tblEvent e 
JOIN tblTABLE pmd ON pmd.record_id = e.reference_id 
JOIN tblTABLE2 pm ON pm.record_id = pmd.t_id 
LEFT OUTER JOIN tblEventRecurring er ON er.event_id = e.record_id 
WHERE e.category = 'CAT' 
AND pm.planet_id = 1 # id of person
AND pm.active = 1 
AND pm.status = 'Read Only' 
AND (
    e.event_date = '2012-02-12' # the event is on current date
    OR ( 
        # recurring with no end date or end date in future
        er.end_date = '0000-00-00' OR er.end_date >= '2012-02-12'
        AND ( 
            e.event_date <= '2012-02-12' # recurring starts today or in past
            AND ( # meets any of the following
                (er.date_part = 'D') 
                OR (er.date_part = 'W' AND dayofweek('2012-02-12') = dayofweek(e.event_date)) 
                OR (er.date_part = 'M' AND dayofmonth('2012-02-12') = dayofmonth(e.event_date)) 
                OR (er.date_part = 'Y' AND (day('2012-02-12') = day(e.event_date) AND MONTH('2012-02-12') = MONTH(e.event_date)))
            ) 
        ) 
    )
)
ORDER BY e.event_time

推荐答案

这与 Mosty 的答案基本相同，但使用 LEFT JOIN 检索仅发生一次的事件.这将检索今天的所有事件.

This is essentially the same as Mosty's answer but with the LEFT JOIN to retrieve events that occur only once. This will retrieve all events for today.

SELECT *
FROM `tblEvent`
LEFT JOIN `tblEventRecurring`
    ON `tblEvent`.`id` = `tblEventRecurring`.`event_id`
WHERE (`tblEvent`.`date` = CURRENT_DATE AND `tblEventRecurring`.`event_id` IS NULL)
OR (
    CURRENT_DATE BETWEEN `tblEvent`.`date` AND `tblEventRecurring`.`end_date`
    AND (
        (`tblEventRecurring`.`date_part` = 'D') OR
        (`tblEventRecurring`.`date_part` = 'W' AND DAYOFWEEK(`tblEvent`.`date`) = DAYOFWEEK(CURRENT_DATE)) OR
        (`tblEventRecurring`.`date_part` = 'M' AND DAYOFMONTH(`tblEvent`.`date`) = DAYOFMONTH(CURRENT_DATE))
    )
)

我不确定这对您是否有用，但我想我会像现在一样发布

I am not sure whether this is of any use to you but thought I would post it as I have done it now

SELECT *
FROM `date_list`
LEFT JOIN (
    SELECT `tblEvent`.`id`, `tblEvent`.`date`, `tblEvent`.`name`, `tblEventRecurring`.`date_part`, `tblEventRecurring`.`end_date`
    FROM `tblEvent`
    LEFT JOIN `tblEventRecurring`
        ON `tblEvent`.`id` = `tblEventRecurring`.`event_id`
) AS `events`
    ON (
        `events`.`date` = `date_list`.`date`
    )
    OR (
        `date_list`.`date` BETWEEN `events`.`date` AND `events`.`end_date`
        AND (
            (`events`.`date_part` = 'D') OR
            (`events`.`date_part` = 'W' AND DAYOFWEEK(`events`.`date`) = DAYOFWEEK(`date_list`.`date`)) OR
            (`events`.`date_part` = 'M' AND DAYOFMONTH(`events`.`date`) = DAYOFMONTH(`date_list`.`date`))
        )
    )
WHERE `date_list`.`date` BETWEEN '2012-02-06' AND '2012-02-12'
ORDER BY `date_list`.`date`;

它假设一个 date_list 表由一个包含一个连续日期列表的 date 字段组成.

It assumes a date_list table consisting of a single date field containing a continuous list of dates.

这篇关于日期部分的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！