mysql - 如何优雅地根据MySQL的main和handle表判断事件处理进度？

Question

我的MYSQL现在有两张表，一张表记录的是需要处理的事件，我们把他称之为main，另一张表是处理结果，我们把他称之为handle。

main和handle之间通过'ticket_id'这个字段确定归属关系，但是ticket_main有一个'ticket_status'字段来确定处理的状态，但是ticket_handle里面的被指定处理人可能会不止一个人，那么每一个被指定的处理人都会在handle表中预先生成一条空白处理结果，方便进行提示，一旦处理完，更新handle表中的内容。

那我应该如何来判断这个事件现在的处理进度？有四种状态，待审核、待处理、待销号、已销号，其他三种都可以通过main字段内容去判断，非常简单。但是“待销号”这个应该是所有人都处理完了，但是还没有确认结果的时候显示。

我应该如何优雅的处理这种显示？
可能有用的字段如下
main: ticket_id, ... , ticket_status, update_time
handle: ticket_id, ... , ticket_handler, ticket_result, update_time

main:
| ticket_id | ticket_status | update_time
| 10000 | TODO | 2024-07-10 11:12:13

handle:
| ticket_id | handle_id | ticket_handle | ticket_result | update_time
| 10000 | 10001 | 张三 | null | null
| 10000 | 10002 | 李四 | 处理结果 | 2024-07-10 12:00:05
或者我应该如何设计数据库，才能实现这个功能？

我试着每次更新处理结果的时候都通过ticket_id把handle表中的所有相同id的内容都拿出来，检查ticket_result是否有内容，如果都有则更新main表中ticket_status字段的内容。
但是这样相当于一次操作进行了3次对表的操作，显得不是很聪明的样子。

Answer 1

要优雅地处理这种显示，你可以使用SQL查询来结合main和handle表，并基于handle表中的ticket_result字段是否都已填写来判断“待销号”状态。这里有一个可能的SQL查询方法，用来判断某个ticket_id是否满足“待销号”状态：

SELECT 
    m.ticket_id,
    m.ticket_status,
    CASE 
        WHEN m.ticket_status IN ('TODO', 'IN_PROGRESS', 'APPROVED') THEN m.ticket_status -- 假设其他三种状态在main表中直接判断
        WHEN COUNT(DISTINCT CASE WHEN h.ticket_result IS NOT NULL THEN h.ticket_handler ELSE NULL END) = COUNT(DISTINCT h.ticket_handler) THEN 'APPROVED_BUT_NOT_CLOSED' -- 如果所有处理人都有结果，则为'待销号'
        ELSE 'PENDING' -- 其他情况可能是'待处理'或其他中间状态
    END AS current_status
FROM 
    main m
LEFT JOIN 
    handle h ON m.ticket_id = h.ticket_id
GROUP BY 
    m.ticket_id, m.ticket_status;

注意：

1. 上面的查询假设main表中ticket_status字段除了'TODO'、'待处理'、'待销号'、'已销号'之外，还有一个'APPROVED'状态表示所有处理人已处理完成但尚未确认结果（或者你可以根据实际需要命名为'待销号'）。
2. 在CASE语句中，我们使用COUNT(DISTINCT ...)来确保我们计算的是不同的处理人，并且只计算那些已经填写了ticket_result字段的处理人。
3. 你可以根据main表中实际的ticket_status字段值来调整CASE语句中的条件。
4. 查询返回的current_status是一个基于查询逻辑计算出的状态，而不是main表中的实际状态。如果需要将这个状态更新回main表，你确实需要执行额外的UPDATE操作，但这可以在需要更新状态时才进行，而不是每次处理结果更新时都进行。
5. 如果main表中没有'APPROVED'这样的中间状态，并且你希望基于handle表来动态计算状态，那么你可以考虑在应用程序逻辑中处理这种状态计算，而不是在数据库中。
6. 为了优化性能，你可能需要为ticket_id在main和handle表上建立索引，以确保JOIN操作的高效性。
7. 这种方法假设你已经有了处理逻辑来确保当所有处理人都完成工作时，ticket_result字段会被填写。如果这不是情况，你可能需要额外的逻辑来确保这一点。

最后，如果你发现经常需要基于handle表来更新main表的状态，并且这成为性能瓶颈，那么你可能需要重新考虑你的数据库设计或应用逻辑，以便更有效地处理这种依赖关系。