MySQL 5.5 - 计算每天有条件的开放项目

Question

我一直在处理一项在 excel 中很容易但在 MySQL 中无法完成的项目。这是一个后续问题，对这个问题有新的值(value)和新的要求: MySQL 5.5 - count open items per day

所以，我在 excel 中又得到了同一个表，我想在 MySQL 中实现 Count_open。Excel的公式是=COUNTIFS($A$2:$A$30000,"<="&E2,$B$2:$B$30000,">="&E2)

所以，在我的 T1 表中，我有两个日期，开盘和闭盘，我想计算每个日期开盘的次数。

以前我在过去 7 天使用临时表，但这次我只需要坚持使用 T1 表。

要获取 T1 表，我使用以下代码:

CREATE TABLE T1
(

ID int (10),
Open_Date date,
Close_Date date);


insert into T1 values (1, '2018-12-17', '2018-12-18');
insert into T1 values (2, '2018-12-18', '2018-12-18');
insert into T1 values (3, '2018-12-18', '2018-12-18');
insert into T1 values (4, '2018-12-19', '2018-12-20');
insert into T1 values (5, '2018-12-19', '2018-12-21');
insert into T1 values (6, '2018-12-20', '2018-12-22');
insert into T1 values (7, '2018-12-20', '2018-12-22');
insert into T1 values (8, '2018-12-21', '2018-12-25');
insert into T1 values (9, '2018-12-22', '2018-12-26');
insert into T1 values (10, '2018-12-23', '2018-12-27');

到目前为止，我已经尝试了以下代码，但没有产生正确的结果。

SELECT T1.Open_Date, count(*) FROM T1
WHERE
    T1.Open_Date>='2018-12-01' and t1.Close_Date <='2019-03-17'

GROUP BY T1.Open_Date;

我现在迷路了，非常需要你的帮助!

Answer 1

Excel 和数据库之间的区别在于您首先在 Excel 中手动生成日期。您也可以在 mysql 中这样做，并为每个日期编写一个查询列表。这与您在 excel 中所做的基本相同。

但幸运的是 mysql 不是 excel，所以我们可以自动化它。首先，我们必须生成一个日期区间。这里有一个大话题:generate days from date range .

然后我们只需要对有效日期进行分组，瞧:

Select a.Date, Count(t.ID)
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a, T1 t
where a.Date between '2018-12-01' and '2019-03-17' 
and a.Date between  t.Open_Date and t.Close_Date
group by a.Date