MySQL跨日期范围的求和值

| 我有一张表，每一行包含一个日期和一些任意的数值。我需要为特定但动态的日期间隔求和。

SELECT VERSION();
5.0.51a-24+lenny5

CREATE TABLE IF NOT EXISTS `work_entries` (
  `entry_id` int(10) unsigned NOT NULL auto_increment,
  `employee_id` tinyint(3) unsigned NOT NULL,
  `work_date` date NOT NULL,
  `hour_count` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`entry_id`)
);

INSERT INTO `work_entries` (`entry_id`, `employee_id`, `work_date`, `hour_count`) VALUES
(1, 1, \'2011-04-25\', 2),
(2, 1, \'2011-04-26\', 3),
(3, 1, \'2011-04-27\', 1),
(4, 2, \'2011-04-25\', 2),
(5, 2, \'2011-04-27\', 4),
(6, 1, \'2011-05-08\', 2),
(7, 2, \'2011-05-06\', 8),
(8, 2, \'2011-05-08\', 9),
(9, 2, \'2011-05-09\', 1),
(10, 1, \'2011-05-29\', 3),
(11, 1, \'2011-05-30\', 1),
(12, 2, \'2011-05-30\', 2),
(13, 1, \'2011-06-02\', 2),
(14, 1, \'2011-06-04\', 3),
(15, 1, \'2011-06-14\', 1),
(16, 2, \'2011-06-14\', 2),
(17, 2, \'2011-06-17\', 4),
(18, 1, \'2011-06-18\', 2),
(19, 2, \'2011-06-19\', 8),
(20, 2, \'2011-06-26\', 9),
(21, 2, \'2011-07-01\', 1),
(22, 1, \'2011-07-03\', 3),
(23, 1, \'2011-07-03\', 1),
(24, 2, \'2011-07-16\', 2);

以下查询返回上述数据集的正确输出，并应说明我正在尝试执行的操作，但是，我需要基于work_date生成年和月值。日子（16、15）永远不变。具体来说，此查询将产生两行，一行用于指定的间隔，另一行用于其余的行，其中每个周期我需要一行（对于N个月的第16个月至N + 1的第15个月，所有月份都有值）。

SELECT
 SUM(hour_count) AS res
 FROM `work_entries`
 GROUP BY work_date
 BETWEEN \'2011-04-16\' AND \'2011-05-15\';

-- Outputs
res
----
44
32

-- Should give
res
----
32
14
28
 2

一个可以正常运行但间隔错误的替代示例（01-31天）：

SELECT
 SUM(hour_count) AS res
 FROM `work_entries`
 GROUP BY MONTH(work_date);

-- Outputs
res
---
12
26
31
 7

此外，如果有一种方法可以同时输出起点和终点，我也很想这样做，但这并不是很重要。

已邀请:

5 个回复

棠媳鳖

可爱：

SELECT
 SUM(hour_count)
 FROM `work_entries`
 GROUP BY
   YEAR(work_date - INTERVAL 15 DAY),
   MONTH(work_date - INTERVAL 15 DAY);

嗜蒂谷尘旱

SELECT
  YEAR(grouping_date) AS year,
  MONTH(grouping_date) AS month,
  SUM(hour_count) AS hour_count_total
FROM (
  SELECT
    work_date,
    hour_count,
    work_date - INTERVAL 15 DAY AS grouping_date
  FROM work_entries
) x
GROUP BY
  YEAR(grouping_date),
  MONTH(grouping_date)

告耸

您可以使用另一个表来存储所有期间范围。

create table `periods` (
  `id` int(11) not null auto_increment,
  `start` date default null,
  `end` date default null,
  primary key (`id`)
) engine=myisam auto_increment=4 default charset=latin1;


insert  into `periods`(`id`,`start`,`end`) values (1,\'2011-04-16\',\'2011-05-15\');
insert  into `periods`(`id`,`start`,`end`) values (2,\'2011-05-16\',\'2011-06-15\');
insert  into `periods`(`id`,`start`,`end`) values (3,\'2011-06-16\',\'2011-07-15\');

并加入

select 
p.start,p.end,
sum(w.hour_count) as total
from periods as p
inner join work_entries as w
on w.work_date between p.start and p.end
group by p.start

+------------+------------+-------+
| start      | end        | total |
+------------+------------+-------+
| 2011-04-16 | 2011-05-15 |    32 |
| 2011-05-16 | 2011-06-15 |     6 |
+------------+------------+-------+
2 rows in set (0.00 sec)

然后，可以在需要时放置where子句。

链眷克袒姜

SELECT
    YEAR(work_date) AS year,
    MONTH(work_date) AS month,
    case
        when right(work_date,2) between \'01\' and \'15\' then 1
        when right(work_date,2) between \'16\' and \'31\' then 2
    end 
    as partofmonth,
    SUM(hour_count) AS totalper15period
FROM work_entries
GROUP BY year, month, partofmonth

筷啸够对铅

有点黑，但是

SELECT 
  SUM(hour_count),
  STR_TO_DATE(
    CONCAT(
      EXTRACT(YEAR_MONTH FROM work_date),
      \'16\'
    ),
    \'%Y%m%d\'
  ) AS startperiod,
  DATE_ADD(
    STR_TO_DATE(
      CONCAT(
        EXTRACT(YEAR_MONTH FROM work_date),
        \'15\'
      ),
      \'%Y%m%d\'
    ),
    INTERVAL 1 MONTH
  ) AS endperiod 
FROM
  work_entries 
GROUP BY work_date BETWEEN startperiod 
  AND endperiod;

考虑一下，以上根本就行不通。根据work_date字段是在生成的开始/结束时间段之内还是之外，它只会返回两行。基本上，您所需的查询需要一个组间隔，该组间隔比日历月更多的是“财政月”。

要回复问题请先登录或注册

MySQL跨日期范围的求和值

5 个回复

发起人

mysql

date

between

问题状态

MySQL跨日期范围的求和值

与内容相关的链接

5 个回复

发起人

mysql

date

between

问题状态