php - 高级 MySQL 区域成本

Question

我对 MySQL 有点熟悉，但考虑实现此功能让我的大脑很受伤。我需要一个系统，您可以在其中设置坐标 X=20 Y=20 WIDTH=20 HEIGHT=20 之间区域的“成本”，每个像素的成本为 15，现在如果您在该区域内放置另一个区域，例如X=25，Y=25，WIDTH=10，HEIGHT=10，每像素 5 的成本，前一个区域被分成 4 个部分，中间被删除以支持这个区域。

我还希望能够计算某些像素之间区域的“成本”。希望我已经以你们大多数人都能理解的方式解释了这一点。我不知道从哪里开始。

Answer 1

我会通过优先存储所有区域然后逐个像素地获取成本来解决这个问题。

因此，单个像素的成本为:

select c.*
from costs c
where PIXELX between c.x and c.x + c.deltax and PIXELY between c.y + c.deltay
order by priority desc
limit 1

要将其扩展到像素区域，您可以将该区域扩展为一组像素。我建议使用 numbers 表来帮助解决这个问题:

select x.num as x, y.num as y
from numbers x cross join
     numbers y
where x.num between PIXELX and PIXELX and DELTAX and
      y.num between PIXELY and PIXELY and DELTAY

现在，结合这些想法以获得给定像素的所有可能成本:

select x.num as x, y.num as y, max(priority) as maxpriority
from numbers x cross join
     numbers y join
     costs c
     on x.num between c.x and c.x + c.deltax and y.num between c.y + c.deltay
where x.value between PIXELX and PIXELX and DELTAX and
      y.value between PIXELY and PIXELY and DELTAY
group by x.num, y.num

最后，加入给定优先级的成本:

select sum(c.cost)
from (select x.num as x, y.num as y, max(priority) as maxpriority
      from numbers x cross join
           numbers y join
           costs c
           on x.num between c.x and c.x + c.deltax and y.num between c.y + c.deltay
      where x.value between PIXELX and PIXELX and DELTAX and
            y.value between PIXELY and PIXELY and DELTAY
      group by x.num, y.num
    ) xyp join
    costs c
    on  xyp.x between c.x and c.x + c.deltax and xyp.y between c.y + c.deltay and
        xyp.maxpriority = c.priority