我试图用我描述的方式更新一棵树。我有一个深度为5的树，叶节点的值大于0。现在，对于每个父级，我要计算子级中访问次数最多的值，并为自己设置该值。现在我正试图这样做：
update itmanagement.PRC_processes parent
set MainStructID = (case when (select MainStructID
from (select * from itmanagement.PRC_processes) child
where parent.ProcessID = child.ParentID
group by MainStructID
order by count(*)
limit 1) is null then 0 else (select MainStructID
from (select * from itmanagement.PRC_processes) child
where parent.ProcessID = child.ParentID
group by MainStructID
order by count(*)
limit 1) end),
SubStructID = (case when (select SubStructID
from (select * from itmanagement.PRC_processes) child
where parent.ProcessID = child.ParentID
group by SubStructID
order by count(*)
limit 1) is null then 0 else (select SubStructID
from (select * from itmanagement.PRC_processes) child
where parent.ProcessID = child.ParentID
group by SubStructID
order by count(*)
limit 1) end)
where ProcessID > 0 and MainStructID = 0 and SubStructID = 0;但是在运行上述查询之后，没有任何变化。例如，id为processid=24的行保留以前的值0。但下面的查询返回值：
select SubStructID
from (select * from itmanagement.PRC_processes) child
where child.ParentID = 24
group by SubStructID
order by count(*)
limit 1;我自己的猜测是select语句返回一个表（也只有一行），它在赋值中不起作用。
那么问题是什么，如何解决呢？
1条答案
按热度按时间sg24os4d1#
我最后把查询改成了另一种方式。我还利用了@solarflare的答案来提高效率。
现在查询如下。我希望它能帮助其他人：
现在一切都好了。