我如何计算特定月份按类别更改/更新的状态 [英] how can i count status changed/updated by categories for a specific month
本文介绍了我如何计算特定月份按类别更改/更新的状态的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想按类别名称计算给定月份的项目状态更新/更改
i want to count projects status updates/changes for a given month by categories names
我有一个类别(类别列表)、投资(投资者投资列表，包括类别中的 category_id)、状态表，其中有一个带有预实施、实施和运营状态的名称.一个项目包含项目 ID、名称、开始日期字段.status 和 projects 都有多对多的关系，称为 project_status 表包含 project_id、status_id、date_of_progress.
I have a categories (list of category), investment (list of investors investments including category_id from categories), status table which has a name with pre implementation, implementation and operations states. a project contains project id, name, start date fields. both status and projects have a many to many relationship called project_status table contains project_id, status_id, date_of_progress.
我想按类别名称统计本月所有更新/更改 status_id 的项目
I want to count all projects within this month who has update/change their status_id by categories names
从预实现到实现从实现到操作从预实施到运营
from pre-implementation to implementations from implementations to operations from pre-implementation to operations
CREATE TABLE `categories` (
`cat_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`description` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `categories` (`cat_id`, `name`, `description`) VALUES
(1, 'agriculture', ''),
(2, 'manufactures ', ''),
(3, 'Technology', ''),
(4, 'services', '');
CREATE TABLE `investments` (
`investment_id` int(11) NOT NULL,
`investment_name` varchar(100) NOT NULL,
`cat_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `investments` (`investment_id`, `investment_name`, `cat_id`) VALUES
(1, 'Company A', 3),
(2, 'Company B', 4),
(3, 'Company C', 1),
(4, 'Company D', 2),
(5, 'Company E', 3),
(6, 'Company F', 4),
(7, 'Company G', 1),
(8, 'Company H', 2);
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, NULL),
(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, NULL),
(6, 2, '2019-08-11 22:07:05', 3, NULL),
(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,
`investment_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `projects` (`project_id`, `name`, `start_date`, `investment_id`) VALUES
(1, 'site A', '2019-02-01 00:00:00', 1),
(2, 'site B', '2019-03-12 00:00:00', 2),
(3, 'Site C', '2019-04-04 00:00:00', 3),
(4, 'Site D', '2019-05-03 00:00:00', 4),
(5, 'site E', '2019-06-01 00:00:00', 5),
(6, 'site F', '2019-08-02 00:00:00', 6),
(7, 'Site J', '2019-08-01 00:00:00', 7),
(8, 'Site H', '2019-08-05 00:00:00', 8);
以下是 2019 年 8 月的结果
Below is the result of Augus 2019
+--------------+-------------------+---------------+---------------+
| category_name| pre_implemntation | imp_operation | pre_operation |
+------+-------+------------+-------------++-------------++--------+
| agriculture | 1 | - | 1 |
| manufactures | 1 | - | - |
| Technology | - | 1 | - |
| services | - | 1 | - |
+--------------+-------------------+---------------+---------------+
推荐答案
SELECT q.year
, q.month
, q.name
, 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
AND (q.previous_status = 1 or q.previous_status IS NULL)
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
, c.name
, ps.project_id
, MAX(status_id) as last_status
FROM project_status ps
JOIN projects p
ON ps.project_id = p. project_id
JOIN investments i
ON p.investment_id = i.investment_id
JOIN categories c
ON i.cat_id = c.cat_id
GROUP BY YEAR(time), MONTH(time), c.name, ps.project_id
) t
) q
GROUP BY q.year, q.month, q.name
输出
+--------+-------+---------------+--------------------+--------------------------+---------------+
| year | month | name | pre_implementation | implementation_operation | pre_operation |
+--------+-------+---------------+--------------------+--------------------------+---------------+
| 2019 | 2 | Technology | 0 | 0 | 0 |
| 2019 | 3 | Technology | 1 | 0 | 0 |
| 2019 | 4 | services | 0 | 0 | 0 |
| 2019 | 5 | agriculture | 0 | 0 | 0 |
+--------+-------+---------------+--------------------+--------------------------+---------------+
| 2019 | 8 | Technology | 0 | 1 | 0 |
| 2019 | 8 | agriculture | 1 | 0 | 1 |
| 2019 | 8 | services | 0 | 0 | 1 |
| 2019 | 8 | manufactures | 1 | 0 | 0 |
+--------+-------+---------------+--------------------+--------------------------+---------------+
这篇关于我如何计算特定月份按类别更改/更新的状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
