如何使用SQL对记录进行全程溯源

Question

我们是一个名为ticketing的表，它跟踪所有的服务票证。一张票证可以指向另一张票证，而另一张票证由下面的replaced_by_ticket_id字段指示

| ticket_id | is_current | replaced_by_ticket_id |
|-----------|------------|-----------------------|
| 134       | 0          | 240                   |
| 240       | 0          | 321                   |
| 321       | 1          | Null                  |
| 34        | 0          | 93                    |
| 25        | 0          | 16                    |
| 16        | 0          | 25                    |
| 93        | 1          | Null                  |

如何编写查询来获取通向当前票证(321 & 93)的票数？我的意思是我可以自己加入这个表，但是没有办法知道要加入多少次。另外，不同的门票有不同的级别。

以下是查询的预期结果

| ticket_id | total_tickets |
|-----------|---------------|
| 321       | 3             |
| 93        | 4             |

做这件事最好的方法是什么？

Answer 1

您可以使用递归查询；诀窍是跟踪原始的“当前”票证，以便您可以根据外部查询中的票证进行聚合。

所以：

with cte as (
    select ticket_id, ticket_id as parent_id from ticketing where is_current = 1
    union all
    select c.ticket_id, t.ticket_id
    from ticket t
    inner join cte c on c.parent_id = t.replaced_by_ticket_id
)
select ticket_id, count(*) total_tickets
from cte
group by ticket_id