尝试完善查询

Question

我只是寻找矩形表单的时刻，所以我试图让它与phpmyadmin上的香草mysql工作，我想有一个数据库，其中有多个区域，可能相互重叠，每个区域都有一个“成本”每平方像素，这样你就可以得到成本在某个点或衡量整个区域的成本，而忽略了被重叠的部分，你也可以获得总成本的平均值存在于数据库中。

我想知道你们中是否有一位mysql老手可以帮我写这样一个查询/数据库模式，我已经或多或少地有了一些东西

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 

这似乎是无效的，充满了错误，也没有提到数据库模式。我花了好几天的时间尝试纠正它并使它工作，但它总是返回null。

提前谢谢，我很感激。

以下是模式:这是数字：

Collation   Attributes  Null    Default Extra   Action
     1  num int(11)         No  None          Change      Drop    Browse distinct values     More

这是成本：

#   Name    Type    Collation   Attributes  Null    Default Extra   Action
 1  x   int(11)         No  None          Change      Drop    Browse distinct values     More
 2  y   int(11)         No  None          Change      Drop    Browse distinct values     More
 3  deltax  int(11)         No  None          Change      Drop    Browse distinct values     More
 4  deltay  int(11)         No  None          Change      Drop    Browse distinct values     More
 5  priority    int(11)         No  None          Change      Drop    Browse distinct values     More

Answer 1

首先更正查询中的错误：

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 + DELTAX)   -- + instead of and
      and (y.value between PIXELY and PIXELY + DELTAY)     -- + instead of and
      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 and c.y + c.deltay)                 -- added c.y
and xyp.maxpriority = c.priority 

然后，如果您仍然没有得到所需的结果，请单独尝试内部查询，看看从内部查询中返回了什么。