mysql - 一个月从另一天开始

Question

我一直在使用 MONTH() 函数来获取月份并按月份分组，就像这个示例查询一样。

SELECT 
    t1.ano,
    t1.mes,
    tempo_extra,
    tempo_ativo,
    tempo_extra / tempo_ativo AS volume_extra
FROM
    (SELECT 
        YEAR(`data`) AS ano,
            MONTH(`data`) AS mes,
            SUM(tempo) AS tempo_extra
    FROM
        rh.aprovacoes
    WHERE
        (tipo = 'BH' OR tipo = 'HE')
            AND estado = 1
            AND YEAR(aprovacoes.`data`) = 2016
    GROUP BY MONTH(`data`)) AS t1
        LEFT JOIN
    (SELECT 
        MONTH(`data`) AS mes, SUM(ativo) AS tempo_ativo
    FROM
        rh.processamento
    GROUP BY MONTH(`data`)) AS t2 ON t1.mes = t2.mes
ORDER BY mes DESC;

如何让月份从上个月的 23 日开始，到当月的 22 日结束。

例如，4 月从 3 月 23 日开始，到 4 月 22 日结束。

Answer 1

只需从您的日期中减去 22 天并添加一个月:

(`data` - interval 22 day) + interval 1 month

• 3 月 22 日 => 2 月 28 日或 29 日 => 3 月 28 日或 29 日
• 3 月 23 日 => 3 月 1 日 => 4 月 1 日
• 4 月 22 日 => 3 月 31 日 => 4 月 30 日
• 4 月 23 日 => 4 月 1 日 => 5 月 1

SQL fiddle :http://sqlfiddle.com/#!9/9eecb7d/54883

顺便说一下，您的查询连接记录与年份无关。我不认为这是需要的，所以在下面的查询中我已经更正了这一点。

SELECT t1.ano, t1.mes, tempo_extra, tempo_ativo, tempo_extra/tempo_ativo AS volume_extra
FROM 
(
  SELECT 
    YEAR(data - interval 22 day + interval 1 month) AS ano,
    MONTH(data - interval 22 day + interval 1 month) AS mes, 
    SUM(tempo) AS tempo_extra
  FROM rh.aprovacoes
  WHERE (tipo = 'BH' OR tipo = 'HE') 
  AND estado = 1 
  AND YEAR(aprovacoes.data - interval 22 day + interval 1 month) = 2016
  GROUP BY 
    YEAR(data - interval 22 day + interval 1 month), 
    MONTH(data - interval 22 day + interval 1 month)
) AS t1
LEFT JOIN 
(
  SELECT 
    YEAR(data - interval 22 day + interval 1 month) AS ano, 
    MONTH(data - interval 22 day + interval 1 month) AS mes, 
    SUM(ativo) AS tempo_ativo
  FROM rh.processamento
  GROUP BY 
    YEAR(data - interval 22 day + interval 1 month), 
    MONTH(data - interval 22 day + interval 1 month)
) AS t2 ON t1.ano = t2.ano AND t1.mes = t2.mes
ORDER BY t1.ano DESC, t1.mes DESC;