mysql - 从数据库中选择最新的历史记录
问题描述
我有一个表order_history
,其中包含我按顺序执行的所有操作的记录,例如confirm order
,或。attempted order
reject order
revert order
当员工在attempt
创建历史记录时标记订单并在一段时间后revert
将该订单管理回其原始位置(它也会创建历史记录)。之后,其他一些员工将相同的订单标记为attempt
。
问题
现在,history
表有 2 条相同顺序的尝试记录。而且我只想选择最新attempted
的历史记录,因为之前的操作已恢复。
数据库结构
|history_id |order_id | date_added |user_id | action_id|
|-----------|---------|--------------|--------|----------|
| 13 | 444 | 2018/07/06 | 9 |2 |
| 12 | 555 | 2018/07/05 | 7 |2 |
| 11 | 444 | 2018/07/05 | 2 |3 |
| 10 | 555 | 2018/07/05 | 2 |3 |
| 9 | 555 | 2018/07/05 | 4 |2 |
| 8 | 444 | 2018/07/04 | 1 |2 |
Where user_id
= Employee and action_id 2 for attempt
and 3 for revert back
,当订单被尝试然后恢复然后其他员工再次尝试时,我的查询在员工 A 和 B 中复制了订单,但它应该显示在最新的员工帐户中。
我的查询
SELECT COUNT(oh.order_id) AS total_attempted,
oh.user_id
FROM `order_history` oh
WHERE oh.action_id = '2'
GROUP BY oh.user_id
结果
此查询显示order ID : 555
给两个用户user_id: 4 and 7
,但订单 555 应该只显示给用户 7。
预期产出
|history_id |order_id | date_added |user_id | action_id|
|-----------|---------|--------------|--------|----------|
| 13 | 444 | 2018/07/06 | 9 |2 |
| 12 | 555 | 2018/07/05 | 7 |2 |
PS:订单 555 的所有操作均在同一日期执行
让我知道是否需要更多详细信息。
解决方案
您的预期输出与您尝试的代码不符。如果您只想要最近的尝试,那么您需要查看尝试和还原。
drop table if exists oh;
create table oh (history_id int,order_id int,date_added varchar(100),user_id int,action_id int);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(13,444,"2018/07/06",9,2);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(12,555,"2018/07/05",7,2);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(11,444,"2018/07/05",2,3);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(10,555,"2018/07/05",2,3);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(9,555,"2018/07/05",4,2);
insert into oh (history_id ,order_id,date_added,user_id,action_id) values(8,444,"2018/07/04",1,2);
insert into oh values(7,333,"2018/07/04",1,3),(6,333,"2018/07/04",1,2),
(5,222,"2018/07/04",1,2),(4,222,"2018/07/04",2,2),
(3,111,"2018/07/04",1,2);
子查询 s 根据 history_id 找到最新的动作(我假设这表示事件的顺序)
此代码列出了最近的尝试
select * from
(
select *
from oh
where action_id in (2,3) and
history_id = (select max(history_id) from oh oh1 where oh1.order_id = oh.order_id)
) s
where s.action_id = 2;
+------------+----------+------------+---------+-----------+
| history_id | order_id | date_added | user_id | action_id |
+------------+----------+------------+---------+-----------+
| 13 | 444 | 2018/07/06 | 9 | 2 |
| 12 | 555 | 2018/07/05 | 7 | 2 |
| 5 | 222 | 2018/07/04 | 1 | 2 |
| 3 | 111 | 2018/07/04 | 1 | 2 |
+------------+----------+------------+---------+-----------+
4 rows in set (0.02 sec)
此代码计算尝试次数(不包括用户的还原)
select user_id,count(*) attempts
from
(
select *
from oh
where action_id in (2,3) and
history_id = (select max(history_id) from oh oh1 where oh1.order_id = oh.order_id)
) s
where s.action_id = 2
group by user_id;
+---------+----------+
| user_id | attempts |
+---------+----------+
| 1 | 2 |
| 7 | 1 |
| 9 | 1 |
+---------+----------+
3 rows in set (0.00 sec)
推荐阅读
- d3.js - D3根据数据数组中的前一个元素定义行
- javascript - 为什么有些 pdf 链接下载文件而其他只是显示它?
- java - SwipeRefreshLayout + AsyncTask:刷新数据失败
- javascript - Jquery Datatable - 从服务器中提取数据 - 如何为列设置类
- networking - 码分多址
- python - Python中断循环并再次进入循环
- arrays - Array Ruby 中实例变量的总和
- ios - 崩溃报告中的非 UI 角色是什么
- javascript - 如何在
- 元素
- mongodb - 环境变量未在 Spring Boot 应用程序启动中绑定