显示标记
考虑这个SQL查询:显示标记
SELECT * FROM
(
SELECT * FROM wp_postmeta
WHERE
(
meta_key = 'latitude' AND meta_value *1 BETWEEN 47.3641471102 AND 47.3731524898)
OR (meta_key = 'longitude' AND meta_value *1 BETWEEN 8.53253429117 AND 8.54583070883)
)
AS PostMeta, wp_posts
WHERE wp_posts.id = PostMeta.post_id order by post_id asc
它给了我符合特定值或特定值之间的经度之间要么纬度的所有记录。我想得到的是符合两个值的记录。 但将查询中的'OR'更改为'AND'将给我零结果。
我想我需要做某种子查询,但不知道如何做到这一点。
有人吗?
你真的只需要从你的经度子查询中分离你的纬度子查询。现在,我有更多的时间,我会更明确地返工查询:
SELECT
/* You're only interested in the wp_posts data, right? */
/* You don't care about the data from wp_postmeta. */
/* Only select the data you're actually going to use. */
/* Ideally you'd specify each column in wp_posts that you're */
/* going to use and ignore the rest */
p.*
FROM
wp_posts p
JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'latitude' AND CAST(meta_value AS DECIMAL) BETWEEN 47.3641471102 AND 47.3731524898) lat ON p.id = lat.post_id
JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'longitude' AND CAST(meta_value AS DECIMAL) BETWEEN 8.53253429117 AND 8.54583070883) long ON p.id = long.post_id
ORDER BY
post_id ASC
......因为没有哪一个wp_postmeta记录将既是纬度和经度,你不能(至少不近就像这样)在相同的WHERE子句中匹配它们。相反,创建两个单独的调用wp_postmeta表,一个纬度和一个经度,并迫使他们都匹配(因此INNER JOIN
,而不是LEFT JOIN
)
我通过很多帮助获得了当前的查询,所以您可以更详细些吗? – moppedisco 2013-05-11 13:57:04
很快写下了我原来的帖子,花了一点时间让它更加明确。 – Jason 2013-05-13 21:04:08
看看[这个问题](HTTP:///*.com/questions/16249731/mysql-select-query-with-multiple-conditions)。此外,你没有得到完全的“半径内的标记”,你需要进一步使用[Haversine公式](http://en.wikipedia.org/wiki/Haversine_formula)或类似的过滤结果。 – DCoder 2013-05-11 11:31:31