Question

我正在对此产生问题,我希望这样做是可能的。

我的表格是一字塔,储存了元数据,因此无法改变栏目和实地数据(容易)。

因此,表结构是

post_id meta_key meta_value

the meta key stores a field name and the meta_value, the value for that field. I need to group these based on the post ID so I can then do a compare between two of the fields. I ve tried all sorts!!

因此,数据如下:

post_id   meta_key        meta_value
1         _wp_field0      10
1         _wp_field1      5
1         _wp_field2      9
1         _wp_field3      "matt s post"
1         _wp_field3      155
2         _wp_field0      51
2         _wp_field1      9
2         _wp_field2      18
2         _wp_field3      "james  post"
2         _wp_field3      199

I ve do a group_CONCAT which Return

post_id     concat
1           10,5,9,matt s post,155
2           51,9,18,James  post,199

这是我最接近的一步,来了解我想要的东西。但是,我不敢肯定的是,如何把这一点分开。我有一整套我想比较的内容(领域1和2)。

我需要上述数字的第二和第三。我能做些什么来解决这些问题? 然后,我想做一些数学。它们是基本浮标,储存着长度和纬度,我想加以比较和距离。

I was thinking of having a temp table? All I need is to reference those two numbers. Maybe I can split the string up by separator??

我已经摆脱了想法。

DROP FUNCTION get_lat;
DELIMITER $$
CREATE FUNCTION get_lat(in_post_id INT)
RETURNS FLOAT
READS SQL DATA
BEGIN
DECLARE latitude FLOAT;
SELECT meta_value INTO latitude FROM wp_postmeta WHERE post_id = in_post_id AND meta_key =  field1 ;
RETURN (latitude);
END $$
DELIMITER;

DROP FUNCTION get_lon;
DELIMITER $$
CREATE FUNCTION get_lon(in_post_id INT)
RETURNS FLOAT
READS SQL DATA
BEGIN
DECLARE longitude FLOAT;
SELECT meta_value INTO longitude FROM wp_postmeta WHERE post_id = in_post_id AND meta_key =  field2 ;
RETURN (longitude);
END $$
DELIMITER;

SELECT post_id,get_lat(post_id)as latitude, get_lon(post_id) as longitude FROM wp_postmeta GROUP BY post_id;

Answer 1

select t1.post_id, t1.meta_value as lat, t2.meta_value as lon
from metatdatatable t1, metadatatable t2
where t1.meta_key = "_wp_field1"
and t2.post_id = t1.post_id
and t2.meta_key = "_wp_field2"

Edit

......为了利用这一方法作为年限/长计数的基础,你可以制作一个温表,或使用类似于下文(简化的多份数)的提问结果。

select d1.post_id, d1.distance
from
(select r1.post_id, ABS($lat - r1.lat) + ABS($lon - r1.lon) as DISTANCE
from (select t1.post_id, t1.meta_value as lat, t2.meta_value as lon
    from metatdatatable t1, metadatatable t2
    where t1.meta_key = "_wp_field1"
    and t2.post_id = t1.post_id
    and t2.meta_key = "_wp_field2") as r1
) as d1
where d1.distance <= 10
order by d1.distance ASC

NB. 在对结果进行昂贵的经度/纬度计算之前，您可能希望对您的经纬度结果应用粗略过滤或存储在临时表中之前进行过滤。想法是忽略所有明显在10英里半径范围之外的r1结果。

如果你使用一个排位表,那将是用户会议的具体内容。

Edit 2

参看。详细情况,但基本上为7分钟的相对照;长度总是超过10英里,因此,如果你的排位和安度记录得长;长度则接近0.117。与你的目标不同,任何点都不能在你10英里半径之内。这意味着,你可以过滤像以下表格:

(select t1.post_id, t1.meta_value as lat, t2.meta_value as lon
        from metatdatatable t1, metadatatable t2
        where t1.meta_key = "_wp_field1"
        and t2.post_id = t1.post_id
        and t2.meta_key = "_wp_field2"
        and ABS(t2.meta_value - $lon) < 0.117
        and ABS(t1.meta_value - $lat) < 0.117
) as r1

NB. 如果你的数据横跨格林尼治、国际日线或赤道,那将不是严格正确的。假设你们的所有排位都是在北美,那就是一个问题。

Answer 2

为了与大家一道坐到桌旁,你需要从桌子和桌子中挑选出一个别处(如你会在实地)。

E.g.

SELECT table1.foo, table2.bar FROM table as table1, table as table2 WHERE table1.foo = table2.bar

条提供了如何使用这一辛迪加的更好例子。

Answer 3

2. 处理这一共同模式:

SELECT lon.post_id post_id, lat.meta_value lat, lon.meta_value lon
FROM wp_postmeta lon
INNER JOIN wp_postmeta lat 
  ON lon.post_id = lat.post_id
WHERE lon.meta_key =  _wp_field1 
AND lat.meta_key =  _wp_field2

Edit

Edit 2

友情链接