本身就是mysql连接表 [英] mysql join table on itself
问题描述
我有一张来自wordpress的表格，用于存储后期元数据，所以列和字段数据无法更改（轻松）。
表结构是这样的：
post_id
meta_key
meta_value
元键存储一个字段名称和meta_value，即该字段的值。
我需要根据帖子ID对它们进行分组，以便我可以在两个字段之间进行比较。我试过各种各样的东西!!
所以数据如下：
post_id meta_key meta_value
1 _wp_field0 10
1 _wp_field1 5
1 _wp_field2 9
1 _wp_field3matt's post
1 _wp_field3 155
2 _wp_field0 51
2 _wp_field1 9
2 _wp_field2 18
2 _wp_field3james'post
2 _wp_field3 199
我做了一个GROUP_CONCAT，它返回
post_id concat
1 10,5,9，马特的职位，155
2 51,9,18，James的职位，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（经度）;
END $$
DELIMITER;
选择post_id，get_lat（post_id）作为纬度，get_lon（post_id）作为经度FROM wp_postmeta GROUP BY post_id;
select t1 .post_id，t1.meta_value as lat，t2.meta_value as long
from metatdatatable t1，metadatatable t2
where t1.meta_key =_wp_field1
and t2.post_id = t1.post_id
和t2.meta_key =_wp_field2
编辑
...以此作为年/月计算的基础，您可以创建一个临时表，或者使用类似于以下（简化的DISTANCE计算）的查询内嵌结果
从
选择d1.post_id，d1.distance
（选择r1.post_id，ABS（$ lat - r1。 （选择t1.post_id，t1.meta_value作为lat，t2.meta_value作为来自metatdatatable t1的lon
，metadatatable t2
）作为DISTANCE
其中t1.meta_key =_wp_field1
和t2.post_id = t1.post_id
和t2.meta_key =_wp_field2）为r1
）为d1
其中d1.distance < = 10
由d1.distance命令ASC
注意。您可能希望在对结果执行昂贵的经纬度/长计算之前，或在将其存储在临时表格之前，对粗/长结果应用粗略过滤器。这个想法应该是在10英里半径之外忽略所有的r1结果。
如果您使用临时表，它将是用户会话特定的。
编辑2
有关详细信息，请参阅地球形状，但基本上是7分钟的拉特&经度总是大于10英里，所以如果你的拉特&多头记录在0.117附近。任何与你的目标相差超过0.117的点不能在你的10英里范围内。这意味着你可以像过滤r1表：
（选择t1.post_id，t1.meta_value为lat，t2.meta_value as $ met $ $ $ b $ metatdatatable t1，metadatatable t2
其中t1.meta_key =_wp_field1
和t2.post_id = t1.post_id
和t2.meta_key =_wp_field2
和ABS（t2.meta_value - $ lon）<0.117
和ABS（t1.meta_value - $ lat）<0.117
）作为r1
注意。如果您的数据跨越格林威治子午线，国际日期线或赤道，这将不是严格正确的。假设你所有的经纪都是北美的，这不会成为问题。
I am having problems with this and I'm hoping it's possible.
I have a table from wordpress which stores post meta data, so the columns and field data cannot be changed (Easily).
The table structure is thus
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!!
So the data would be as follows:
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 done a GROUP_CONCAT which returns
post_id concat
1 10,5,9,matt's post,155
2 51,9,18,James' post,199
that is the closest I've come to getting what I want. But I am not sure how to then split that up. I have a whole series of ints which I want to compare (fields1 and 2).
I need the second and third numbers from the string above. Can I do something in SQL to get these? I then want to do some maths on it. They are basically floats which store longitude and latitude which I want to compare and get distance....
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??
I've run out of ideas. (ps) I've also tried to write my own function (as below) but this crashed the server several times!!
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;
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
...to use this as the basis for yr lat/long calc, you can either create a temp table, or use the results inline with a query similar to the (simplified DISTANCE calc) below
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. you might want to apply a rough filter to your lat/long results before doing the 'expensive' lat/long calculation on the results, or before storing in a temp table. The idea would be to ignore all of the r1 results clearly outside of a 10 mile radius.
If you use a temp table it will be user session specific.
Edit 2
Have a look at Shape of the Earth for details, but essentially 7 minutes lat & longitude is always greater than 10 miles, so if your lat & longs are recorded in degrees this is 0.117 near enough. Any point differing by more than 0.117 from your target cannot be inside your 10 mile radius. This means you can filter the r1 table like :
(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. If your data spans the Greenwich Meridian, International Date Line or Equator, this will not be strictly correct. Assuming all of your lat/longs are for North America it won't be a problem.
这篇关于本身就是mysql连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
