mysql - 我如何在 mysql 中获取当前期间或最近的周年纪念日(月或年)？

Question

我有两个表(尽管它们可以合并为一个):一个有周期指示符(0 = 年，1 = 月)，另一个包含开始日期。一个时期可以是一个月或一年，我需要找出当前时期是什么时候。

表A:

id  type
7       1 
269     1
270     1
271     0

表 B:

id  start   end
7   24-11-2013  31-12-2099
9   24-11-2013  31-12-2099
10  28-11-2013  28-11-2014
271 20-12-2013  31-12-2099

现在我需要为表 B 中的每个 id 找到当前期间开始的日期。例如:

1. id 7 以“24-11-2013”​​开始，类型 = 1(每月)，今天是“07-05-2015”-> 当前开始是“24-04-2015”
2. id 271 以“20-12-2013”​​开始，类型 = 0(每年)，今天是“07-05-2015”-> 当前开始是“20-12-2014”

我可以只实现纯 MySQL 吗？

编辑:请注意，我对日期使用了 DD-MM-YYYY 表示法。如果这令人困惑，我深表歉意

Answer 1

有趣的案例。我们可以使用条件语句来实现，该语句根据当前日期和开始日期之间的关系采取不同的操作。

set @when = "2015-03-01";

select b.start, 
       b.end, 
       case when type = 1 then
         case 
           when day(@when) < day(b.start) then
             b.start + interval abs(
               period_diff(
                 concat(
                   year(b.start), 
                   lpad(month(b.start), 2, "0")
                  ), 
                  concat(
                    year(@when), 
                    lpad(month(@when), 2, "0")
                  )
               )) -1 month 
           when day(@when) >= day(b.start) then
             b.start + interval abs(
               period_diff(
                 concat(
                   year(b.start), 
                   lpad(month(b.start), 2, "0")
                  ), 
                  concat(
                    year(@when), 
                    lpad(month(@when), 2, "0")
                  )
               )) month          
         end 
         when type = 0 then
           b.start + interval (year(@when) - YEAR(b.start) - (date_format(@when, '%m%d') < DATE_FORMAT(b.start, '%m%d'))) year          
         end current_period_start
  from tableb b
    inner join tablea a
      on b.id = a.id;

它应该正确处理诸如闰年、二月比其他月份天数少等问题。在这种情况下，我使用了一个变量来保存当前日期，只是为了帮助测试这些不同的场景。您可以简单地将查询中的所有位置的 @when 替换为 now() 以与当前日期进行比较。

period_diff 是一个计算两个日期之间的月差的函数，但它是愚蠢的，因为它不接受实际的日期参数，而是采用 'yyyymm' 形式的字符串.

无论如何，就查询而言，如果 type = 1，它会找出两个日期之间的月份差异，并将其添加到开始日期以获得最近的每月周年纪念日。它还会检查一个月中的第几天，以查看周年纪念日是在当前月份还是前一个月，如果是前一个月，则从计算出的差值中减去 1，因为 period_diff 是愚蠢的并且不会'考虑天数。

年份计算更简单，只需返回年份差值，然后减去 1 或 0，具体取决于我们的月份和日期是否小于起始月份和日期。

有一个演示 here