我如何通过更改/更新来计算项目状态 [英] How can i count project status by change/update made
本文介绍了我如何通过更改/更新来计算项目状态的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想通过 id 从到
I want to count status change by id from to
我有一个 mysql 状态表，它有一个带有预实现、实现和操作状态的名称.
I have a mysql status table which has a name with pre implementation, implementation and operations states.
- 一个项目包含项目 ID、名称、开始日期字段.
- status 和projects 都有多对多的关系，称为project_status 表包含project_id、status_id、date_of_progress.
所以我想统计本月所有更新/更改 status_id 的项目
so i want to count all projects within this month who has update/change their status_id
- 从预实施到实施
- 从实现到操作
- 从预实施到运营
--
CREATE TABLE `status` (
`status_id` int(11) NOT NULL,
`status_name` varchar(30) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`status_name_tg` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `status` (`status_id`, `status_name`, `description`, `status_name_tg`) VALUES
(1, 'Pre Implementation', 'Operational', 'Pre Implementation'),
(2, 'Implementation', NULL, 'Implementation'),
(3, 'Operational', NULL, 'Operational'),
(4, 'Inactive', NULL, 'Inactive'),
(5, 'Cancellation', NULL, 'Cancellation');
CREATE TABLE `project_status` (
`project_status_id` int(11) NOT NULL,
`status_id` int(11) NOT NULL,
`time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`project_id` int(11) NOT NULL,
`reason_for_cancellation` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `project_status` (`project_status_id`, `status_id`, `time`, `project_id`, `reason_for_cancellation`) VALUES
(1, 1, '2019-02-24 21:51:50', 1, NULL),
(2, 2, '2019-03-26 21:52:57', 1, ' '),
(3, 1, '2019-04-30 21:57:57', 2, NULL),
(4, 1, '2019-05-26 22:04:08', 3, NULL),
(5, 3, '2019-08-24 22:06:36', 1, ' '),
(6, 2, '2019-08-11 22:07:05', 3, ' '),
(8, 1, '2019-08-01 00:14:41', 6, NULL),
(9, 1, '2019-08-09 12:11:22', 7, NULL),
(10, 1, '2019-08-09 12:15:22', 8, NULL),
(11, 3, '2019-08-14 10:07:49', 7, NULL),
(12, 2, '2019-08-14 10:10:45', 8, NULL),
(13, 2, '2019-08-26 17:16:02', 6, 'NULL');
(14, 3, '2019-08-26 17:16:02', 6, 'NULL');
CREATE TABLE `projects` (
`project_id` int(11) NOT NULL,
`name` varchar(150) NOT NULL,
`start_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `projects` (`project_id`, `name`, `start_date`) VALUES
(1, 'site A', '2019-02-01 00:00:00'),
(2, 'site B', '2019-03-12 00:00:00'),
(3, 'Site C', '2019-04-04 00:00:00'),
(4, 'Site D', '2019-05-03 00:00:00'),
(5, 'site E', '2019-06-01 00:00:00'),
(6, 'site F', '2019-08-02 00:00:00'),
(7, 'Site J', '2019-08-01 00:00:00'),
(8, 'Site H', '2019-08-05 00:00:00');
欲望输出
|pre - implementations|implementations - operations| pre - operations|
|2 | 1 |2 |
推荐答案
我认为关键是找到每个月每个项目的最后状态是什么:
I think the key is find what is the last status for each project on each month:
SELECT YEAR(time) as year
, MONTH(time) as month
, project_id
, MAX(status_id) as last_status
FROM project_status
WHERE status_id < 4 -- ignore (inactive, cancellation)
GROUP BY YEAR(time), MONTH(time), project_id;
输出
+------+-------+------------+-------------+
| year | month | project_id | last_status |
+------+-------+------------+-------------+
| 2019 | 2 | 1 | 1 |
| 2019 | 3 | 1 | 2 |
| 2019 | 4 | 2 | 1 |
| 2019 | 5 | 3 | 1 |
| 2019 | 8 | 1 | 3 |
| 2019 | 8 | 3 | 2 |
| 2019 | 8 | 6 | 2 |
| 2019 | 8 | 7 | 3 |
| 2019 | 8 | 8 | 2 |
+------+-------+------------+-------------+
现在使用相关查询，您可以找到当月之前的最后状态:
Now using a correlated query you can found out the last status before the current month:
SELECT *, (SELECT MAX(p.status_id)
FROM project_status p
WHERE p.time < CONCAT(t.year,'/', t.month,'/1')
AND p.project_id = t.project_id
) as previous_status
FROM (
SELECT YEAR(time) as year
, MONTH(time) as month
, project_id
, MAX(status_id) as last_status
FROM project_status
WHERE status_id < 4
GROUP BY YEAR(time), MONTH(time), project_id
) t
输出
+------+-------+------------+-------------+-----------------+
| year | month | project_id | last_status | previous_status |
+------+-------+------------+-------------+-----------------+
| 2019 | 2 | 1 | 1 | |
| 2019 | 3 | 1 | 2 | 1 |
| 2019 | 4 | 2 | 1 | |
| 2019 | 5 | 3 | 1 | |
| 2019 | 8 | 1 | 3 | 2 |
| 2019 | 8 | 3 | 2 | 1 |
| 2019 | 8 | 6 | 2 | |
| 2019 | 8 | 7 | 3 | |
| 2019 | 8 | 8 | 2 | |
+------+-------+------------+-------------+-----------------+
现在只做一些条件计数
SELECT q.year
, q.month
, COUNT(CASE WHEN q.last_status = 2 THEN 1 END) as pre_implementation
, COUNT(CASE WHEN q.last_status = 3
AND q.previous_status = 2 THEN 1 END) as implementation_operation
, COUNT(CASE WHEN q.last_status = 3 THEN 1 END) as pre_operation
FROM (
SELECT *, (SELECT MAX(p.status_id)
FROM project_status p
WHERE p.time < CONCAT(t.year,'/', t.month,'/1')
AND p.project_id = t.project_id
) as previous_status
FROM (
SELECT YEAR(time) as year
, MONTH(time) as month
, project_id
, MAX(status_id) as last_status
FROM project_status
WHERE status_id < 4
GROUP BY YEAR(time), MONTH(time), project_id
) t
) q
GROUP BY q.year, q.month
输出
+------+-------+--------------------+--------------------------+---------------+
| year | month | pre_implementation | implementation_operation | pre_operation |
+------+-------+--------------------+--------------------------+---------------+
| 2019 | 2 | 0 | 0 | 0 |
| 2019 | 3 | 1 | 0 | 0 |
| 2019 | 4 | 0 | 0 | 0 |
| 2019 | 5 | 0 | 0 | 0 |
| 2019 | 8 | 3 | 1 | 2 |
+------+-------+--------------------+--------------------------+---------------+
这篇关于我如何通过更改/更新来计算项目状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
