Question

我有一个账户表,有专栏,用于起始日期和结束日期。我希望用户提供起始日期和结束日期,如果账户在规定的开端日期和结束日期之间随时活跃,则所有数据必须退还。

如果账户目前仍在运行,最终日期可能无效。

The account must be selected if it was open at any time between the user specified range which means that even accounts that were opened or closed between the range must be included.

这里是我想要的情景。

https://i.stack.imgur.com/Gtqu5.gif” alt=“alt text”/>

黄色部分是用户进入的日期范围。绿色部分是我所希望选择的内容。小规模红 part是我不想选择的东西。 G、H和I的末端arrow指这些账户没有结束日期。没有任何arrow子的所有其他人都有结束日期。

Answer 1

因此,这笔数额用于核实账户的起算日期是在用户提供截止日期之前,账户的结束日期是在用户提供起算日期之后(或无效,在这种情况下,我们甚至不需要核对):

where
    account.start_date < @user_end_date and
    (account.end_date >= @user_start_date or account.end_date is null)

Answer 2

SELECT
    *
FROM
    accounts
WHERE
    (start_date >= :user_supplied_start AND start_date <= :user_supplied_end)
OR
    (end_date >= :user_supplied_end AND end_date <= :user_supplied_start)
OR
    (start_date <= :user_supplied_start AND end_date IS NULL)

Answer 3

SELECT  *
FROM    accounts
WHERE   start_date <= @input_end_date
        AND (end_date >= @input_start_date OR (end_date IS NULL AND NOW() >= @input_start_date))

友情链接