MySQL 5.5-每天计算未清项目 [英] MySQL 5.5 - count open items per day
问题描述
我有一张下表只是一个快照,我要做的就是计算每个日期未清项目的数量.
I have the below table that is just a snapshot and all I want to do is to calculate the number of open items per date.
我曾经在Excel中使用简单的公式=COUNTIFS($A$2:$A$30000,"<="&E2,$B$2:$B$30000,">="&E2)
来做到这一点,其中A列是Open_Date日期,B列是Close_Date日期.我想使用SQL来获得相同的结果.
I used to do it in excel with simple formula =COUNTIFS($A$2:$A$30000,"<="&E2,$B$2:$B$30000,">="&E2)
where column A was the Open_Date dates and column B the Close_Date dates. I want to use SQL to get the same results.
这是我的excel快照.上面的公式.
This is my excel snapshot. Formula above.
在mysql中,我已使用T1表将其复制:
In mysql I have replicated it with T1 table:
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');
第一步是创建带有日期的表,以防Date_open中有任何间隙.因此,目前我的代码是
First step was to create the table with dates in case there any gap in Date_open. So my code at the moment is
SELECT
d.dt, Temp_T1.*
FROM
(
SELECT '2018-12-17' AS dt UNION ALL
SELECT '2018-12-18' UNION ALL
SELECT '2018-12-19' UNION ALL
SELECT '2018-12-20' UNION ALL
SELECT '2018-12-21' UNION ALL
SELECT '2018-12-22' UNION ALL
SELECT '2018-12-23' UNION ALL
SELECT '2018-12-24'
) d
LEFT JOIN
(SELECT * FROM T1) AS Temp_T1
ON Temp_T1.Open_Date = d.dt
我不知道如何计算与excel中相同的值?
I am lost how to calculate the same values as I do in excel?
推荐答案
您想使用GROUP BY为d
派生表中的每个日期添加一行.
You want to use GROUP BY to make one row for each date in your d
derived table.
然后将d
连接到t1
表,其中d.dt
在打开和关闭日期之间.
Then join d
to the t1
table where the d.dt
is between the open and close dates.
SELECT
d.dt, COUNT(*) AS open_items
FROM
(
SELECT '2018-12-17' AS dt UNION ALL
SELECT '2018-12-18' UNION ALL
SELECT '2018-12-19' UNION ALL
SELECT '2018-12-20' UNION ALL
SELECT '2018-12-21' UNION ALL
SELECT '2018-12-22' UNION ALL
SELECT '2018-12-23' UNION ALL
SELECT '2018-12-24'
) d
LEFT JOIN T1 ON d.dt BETWEEN t1.Open_Date and t1.Close_Date
GROUP BY d.dt;
输出:
+------------+------------+
| dt | open_items |
+------------+------------+
| 2018-12-17 | 1 |
| 2018-12-18 | 3 |
| 2018-12-19 | 2 |
| 2018-12-20 | 4 |
| 2018-12-21 | 4 |
| 2018-12-22 | 4 |
| 2018-12-23 | 3 |
| 2018-12-24 | 3 |
+------------+------------+
这篇关于MySQL 5.5-每天计算未清项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!