在表中递归查找内容的最有效方法? [英] Most efficient way to find something recursively in a table?

问题描述

我有一个表 users 包含以下列

I have a table users with the following columns

id INT PRIMARY
username
target
dead

target 包含同一表中另一个用户的 ID.所有用户都以 dead 为 0 开始.如果 dead 为 1，则表示他们已死亡.随着时间的推移，dead 可以改变，但目标列将始终保持在它开始的位置.

target contains an id of another user in the same table. All users start out with dead as 0. If dead is 1, then that means they're dead. As time goes on, dead can change, but target column will always stay with whatever it started with.

如果用户死亡，他们的目标将成为杀死他们的新目标.因此，如果我试图找到用户 A 的当前目标，我必须首先找到他们的原始目标，如果该目标已死，则移动到该目标的目标上，依此类推，直到找到未死的目标.这将是用户 A 的当前目标.

If a user dies, their target becomes whoever killed them's new target. So if I'm trying to find the current target of user A, I would have to first find their original target, and if that target is dead, move onto that target's target, and so on, until I find one that's not dead. and that would be the current target of user A.

目前我有这个查询，它只是给我原始目标

Currently I have this query that just gives me the original target

SELECT `a`.`username`, `a`.`dead`, `b`.`username` 
FROM `users` AS `a` LEFT JOIN (`users` AS `b`) 
ON (`a`.`target` = `b`.`id`)

但不知道如何添加以给我当前的目标.

But have no idea how to add to it to give me the current target.

推荐答案

递归使用représentation intervallaire"更有效，查看http://sqlpro.developpez.com/cours/arborescence/.

Recursivity is used more effectively with "représentation intervallaire", take a look on http://sqlpro.developpez.com/cours/arborescence/.

这篇关于在表中递归查找内容的最有效方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！