我有一个表，其中关闭日期将在关闭时更新...直到该字段为空。我需要显示从一开始就每 7 天分组的紧密趋势。因此，例如，如果有 10 行并且在第一周关闭了 2 行，那么该周的总数应该是 8。我已经能够创建一个查询来显示每周关闭了多少行，但我正在努力找到一种方法来计算前几周的总数。

表格

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `test_data`;
CREATE TABLE `test_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `close_date` date DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
INSERT INTO `test_data` VALUES ('1', '2015-02-02', 'one');
INSERT INTO `test_data` VALUES ('2', '2015-02-02', 'one');
INSERT INTO `test_data` VALUES ('3', '2015-02-09', 'one');
INSERT INTO `test_data` VALUES ('4', '2015-02-09', 'one');
INSERT INTO `test_data` VALUES ('5', '2015-02-09', 'one');
INSERT INTO `test_data` VALUES ('6', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('7', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('8', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('9', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('10', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('11', '2015-02-02', 'two');
INSERT INTO `test_data` VALUES ('12', '2015-02-02', 'two');
INSERT INTO `test_data` VALUES ('13', '2015-02-09', 'two');
INSERT INTO `test_data` VALUES ('14', '2015-02-09', 'two');
INSERT INTO `test_data` VALUES ('15', '2015-02-09', 'two');
INSERT INTO `test_data` VALUES ('16', '2015-02-16', 'two');
INSERT INTO `test_data` VALUES ('17', '2015-02-16', 'two');
INSERT INTO `test_data` VALUES ('18', '2015-02-16', 'two');
INSERT INTO `test_data` VALUES ('19', '2015-02-16', 'two');
INSERT INTO `test_data` VALUES ('20', '2015-02-16', 'two');

到目前为止的查询

select
'2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK as start_week,
(SELECT COUNT(*) FROM test_data WHERE location = 'one') - COUNT(a.id) AS one,
(SELECT COUNT(*) FROM test_data WHERE location = 'two') - COUNT(b.id) AS two
from test_data
left join test_data as a on a.id = test_data.id and a.location = 'one'
left join test_data as b on b.id = test_data.id and b.location = 'two'
where test_data.close_date >= '2015-02-02'
group by DATEDIFF(test_data.close_date,'2015-02-02') DIV 7

输出

start_week one two
2015-02-02 8   8
2015-02-09 7   7
2015-02-16 5   5

我要实现的输出

start_week one two
2015-02-02 8   8
2015-02-09 5   5
2015-02-16 0   0

在我继续努力的过程中，向正确方向的插入表示赞赏。

编辑:对预期结果的更多解释。

正如您在“输出”中看到的那样，它正在计算从总数中减去的关闭数。对于位置“一”，有 10 个条目，在第 1 周有 2 个关闭(所以 8 个在第 1 周仍然开放)，在第 2 周有 3 个关闭，因此自开始以来关闭的总数为 5 到第 2 周的总数应该是 5。

所以对于我想要的输出。

start_week one 
2015-02-02  8  <-- Total 10 - 2 closed in this week = 8
2015-02-09  5  <-- Total 10 - (3 closed in this week + the 2 in week 1) = 5
2015-02-16  0  <-- Total 10 - (5 closed in this week + the previous weeks) = 0

最佳答案

a1 和 a2 列是您正在寻找的答案。我在没有派生表的情况下尝试了这个没有得到正确的答案。我有一个 mysql 错误或误解。但是对于派生表，它似乎工作正常。

    SELECT
sums.start_week,sums.*,
 sums.tot1-sums.cur1-sums.back1 a1,sums.tot2-sums.cur1-sums.back2 a2
  FROM
( /* Derived table cf counts */
SELECT DATEDIFF(test_data.close_date,'2015-02-02') DIV 7 AS sd,
'2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK as start_week,
(select COUNT(*) from test_data d1 where d1.location='one') tot1,
(select COUNT(*) from test_data d2 where d2.location='two') tot2,
count(CASE WHEN test_data.location='one' then 1 else null end )  cur1,
count(CASE WHEN test_data.location='two' then 1 else null end
    )    cur2,

(select count(*) from test_data d1 where d1.location='one' AND d1.close_date<
    ('2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK) ) as back1,
(select count(*) from test_data d2 where d2.location='two' AND d2.close_date<
    ('2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK) ) as back2

from test_data

where test_data.close_date >= '2015-02-02'
group by DATEDIFF(test_data.close_date,'2015-02-02') DIV 7 ,
'2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK 
) sums

关于MySQL查询以显示每周的运行总计，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/28796554/