mysql本身的联接表

Question

我对此有疑问，我希望这是可能的。

我有一个来自wordpress的表，用于存储发布的元数据，因此无法（轻松）更改列和字段数据。

因此表结构为

post_id meta_key meta_value

meta键存储字段名称和meta_value，即该字段的值。我需要根据帖子ID对它们进行分组，以便可以在两个字段之间进行比较。我尝试了各种！

因此数据如下：

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

我已经完成了GROUP_CONCAT，它返回

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

那是我得到我想要的最接近的东西。但是我不确定如何将其拆分。我有一系列要比较的整数（字段1和2）。

我需要上面字符串中的第二个和第三个数字。我可以在SQL中做一些事情来获得这些吗？然后，我想对此做一些数学运算。它们基本上是浮点数，用于存储经度和纬度，我想比较它们并获取距离…。

我在想有一个临时表吗？我需要引用的是这两个数字。也许我可以用分隔符将字符串分割开？？

我的想法已经用完了。（ps）我也尝试编写自己的函数（如下所示），但这多次导致服务器崩溃！

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"

编辑

…要以此为基础进行年经/纬度计算，您可以创建一个临时表，也可以将结果内联用于与以下查询类似的查询（简化的DISTANCE calc）

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

注意
您可能需要对经纬度结果进行粗略筛选，然后再对结果进行“昂贵”的经纬度计算，或者在存储到临时表中之前。想法是在半径10英里以外的地方完全忽略所有r1结果。

如果使用临时表，它将是特定于用户会话的。

编辑2

请查看“地球形状”以获取详细信息，但是纬度和经度本质上讲7分钟始终大于10英里，因此，如果以度为单位记录纬度和经度，则表明该纬度足够接近0.117。与目标相差超过0.117的任何点都不能在半径10英里范围内。这意味着您可以像下面那样过滤r1表：

(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

注意 如果您的数据跨越格林威治子午线，国际日期变更线或赤道，那么这将不是严格正确的。假设您所有的经纬度都用于北美，那将不是问题。