我有一个WordPress数据库，在wp_postteta表中有meta_key和meta_value列。我正在尝试选择meta_key为sale_price且meta_值大于0的所有行：
SELECT * FROM `ra_postmeta` WHERE `meta_key` = '_sale_price' AND `meta_value` > 0
这很管用。不过，我想进一步完善这些结果。还有两个
meta_key需要我处理，_sale_price_dates_from和_sale_price_dates_to。我只想包含今天的strottotime日期（例如1406649975在_sale_price_dates_from和_sale_price_dates_to范围内）的行。这是因为有些产品的销售价格大于0，但计划稍后再销售，我不希望在结果中返回这些产品。例如。_sale_price_dates_from >= 1406649975 AND _sale_price_dates_to <= 1406649975
但我对SQL还不够了解。
是不是有点像：
SELECT * FROM `ra_postmeta`
WHERE (`meta_key` = '_sale_price' AND `meta_value` > 0)
AND (`meta_key` = '_sale_price_dates_to' AND ( `meta_value` >= 1406649975 OR `meta_value`=null))
AND (`meta_key` = '_sale_price_dates_from' AND ( `meta_value` <= 1406649975 OR `meta_value`=null))
（但这将返回零行）
谢谢
最佳答案
这是一个典型的困难（一个灵活的）EAV model。
对于每种类型的元数据，都需要自联接表一次：
SELECT sale_price.post_id
-- first, let's check for the sale price
FROM ra_postmeta AS sale_price
-- then we need to check the "dates to"
-- the LEFT JOIN, instead of (INNER) JOIN, makes the query return all rows from sale_price
-- even if there is no match on "dates_to" (which equates to your "OR meta_value=NULL") clause
LEFT JOIN ra_postmeta AS dates_to
ON dates_to.meta_id = sale_price.meta_id -- this is the real JOIN condition
AND dates_to.meta_key = '_sale_price_dates_to' -- this selects only meta-data related to "dates to"
AND dates_to.meta_value >= 1406649975 -- condition on this meta-data
-- then we need to check the "dates from", we take the exact same approach as with "dates to"
LEFT JOIN ra_postmeta AS dates_from
ON dates_from.meta_id = sale_price.meta_id
AND dates_from.meta_key = '_sale_price_dates_from'
AND dates_from.meta_value <= 1406649975
-- finally, the conditions on the first table (in the FROM clause)
WHERE
sale_price.meta_key = '_sale_price'
AND sale_price.meta_value > 0
关于mysql - 合并来自MYSQL的Select值，然后精炼落在一定范围内的值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25020735/