带有日期范围分析的 MYSQL 交叉表 : days as rows, sum as columns

Question

我有一个像这样的表格，显示了患者接受治疗的日期:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `datefrom` datetime DEFAULT NULL,
  `timefrom` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`));

我的数据如下所示:

SELECT id, datefrom, dateto FROM table1;

1022, 2016-03-16 00:00:00, 2016-03-18 00:00:00
1023, 2016-03-18 00:00:00, 2016-03-24 00:00:00
1024, 2016-03-14 00:00:00, 0000-00-00 00:00:00
1025, 2016-03-29 00:00:00, 2016-03-31 00:00:00
1026, 2016-03-29 00:00:00, 2016-03-30 00:00:00
1027, 2016-03-30 00:00:00, 2016-03-31 00:00:00
1028, 2016-03-31 00:00:00, 0000-00-00 00:00:00

我有兴趣显示带有 的交叉表

• 以行表示的天数(从 datefrom 的最小值开始，到 dateto 的最大值结束)
• 在这一天接受治疗的患者总数

此外，如果没有给出dateto，则患者将一直服用到今天。

上述数据的预期结果应如下所示(仅第一行):

2016-03-14, 2
2016-03-15, 1
2016-03-16, 2
2016-03-17, 2
2016-03-18, 3
2016-03-19, 2
2016-03-20, 2

Answer 1

您可以引入包含所有日期的附属表(可以创建一次并填充全天日期)。然后就可以使用

SELECT adt.date_column,
       COUNT(*)
FROM the_all_dates_table adt
     JOIN table1 t1 ON adt.date_column BETWEEN t1.datefrom AND t1.dateto
GROUP BY adt.date_column