我正在使用mysql来计算多边形要素(postsec)内的点要素(商店)的数量。

我的选择查询工作正常,但我想用生成的结果更新表postsec。我的查询如下:

select pc_sec, count(*) as count from shops
join postsec on st_within(shops.SHAPE,postsec.SHAPE)
where shops.shop_cat > 0 and shop_cat < 10 group by pc_sec


我已经尝试了多种方法来执行此操作,例如更新postsec设置new_column =([选择查询]),但似乎找不到正确的方法。

最佳答案

这样的事情应该工作

update postsec
inner join
(
   select pc_sec, count(*) as pseccount
   from shops
   join postsec on st_within(shops.SHAPE,postsec.SHAPE)
   where shops.shop_cat between 1 and 9
   group by pc_sec
) x on x.pc_sec = postsec.id
set count_column = x.pseccount

关于mysql - 使用空间查询的结果更新mysql表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/20924442/

10-11 03:32