MySQL 根据字段值忽略(接近)重复项

Question

我正在处理一个处理“门票”的表(状态=“开放”或状态=“关闭”)。当票证关闭时，相关系统不会更改状态，而是会创建一个具有“已关闭”状态的重复条目。

对于“ticket_number”关键字段，如果存在同一“ticket_number”字段的另一条记录且状态为“已关闭”，我如何修改下面的查询以忽略任何状态=“打开”的项目？

我无法使用“DISTINCT”，而且我对如何根据字段值有选择地从查询中过滤(接近)重复记录有点迷失。

感谢您的帮助。

SELECT (@cnt := @cnt + 1) AS item_number, t.*
FROM test t
CROSS JOIN (SELECT @cnt := 0) AS dummy
WHERE created >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND created<= NOW()
ORDER BY created_at DESC

Answer 1

如果数据中有一些内容可以标识工单状态更改的顺序，例如自动增量 ID 字段或日期，那么您可以选择最后一个状态事件

drop table if exists t;
create table t(id int auto_increment primary key,ticket_number int, status varchar(6));

insert into t (ticket_number,status) values
(1,'open'),(2,'open'),(2,'closed'),(3,'open'),(3,'closed'),(3,'open'),
(4,'open'),(4,'closed'),(4,'open'),(4,'closed');

select * 
from t 
where id = (select max(t1.id) from t t1 where t1.ticket_number = t.ticket_number)
        and t.status = 'open';
MariaDB [sandbox]> select *
    -> from t
    -> where id = (select max(t1.id) from t t1 where t1.ticket_number = t.ticket_number)
    -> and t.status = 'open';

+----+---------------+--------+
| id | ticket_number | status |
+----+---------------+--------+
|  1 |             1 | open   |
|  6 |             3 | open   |
+----+---------------+--------+
2 rows in set (0.00 sec)