在我的项目中,我有来自mysql的wordpress自定义字段数据:
---------------------------------------------------
id | post_id | meta_key | meta_value
---------------------------------------------------
1 | 200 | age_min | 5
2 | 200 | age_max | 8
3 | 399 | ... | ...
我的表结构
id => aut_increment
meta_key => varchar(255)
meta_value => longtext
从我的sql脚本中,我必须在"age_min和age_max"和子项编号之间找到子项:
SELECT DISTINCT p.ID
, a.meta_value as nbrmin, b.meta_value as nbr_enfants_min , c.meta_value as age_min, d.meta_value as age_max
FROM `6288gjvs_posts` as p
LEFT JOIN 6288gjvs_postmeta as a ON a.post_id = p.ID
LEFT JOIN 6288gjvs_postmeta as b ON b.post_id = p.ID
LEFT JOIN 6288gjvs_postmeta as c ON c.post_id = p.ID
LEFT JOIN 6288gjvs_postmeta as d ON d.post_id = p.ID
WHERE p.post_type = 'product' AND post_status = 'publish'
AND a.meta_key = 'childs_min_number'
AND a.meta_value <= 2
AND b.meta_key = 'childs_max_number'
AND b.meta_value >= 2
AND c.meta_key = 'age_min'
AND c.meta_value = 1
AND d.meta_key = 'age_max'
AND d.meta_value >= 10
当我启动此查询时,这无法正常工作,因为meta_value类型是长文本,而我的 php 变量($this->age_min、$this->age_max )是整数,所以我无法比较这两种不同的类型?
我试图像这样转换,但没有运气:
... CONVERT(a.meta_value, DECIMAL) ...
如本网站所述:Mysql 转换函数
有人可以帮助我吗?
谢谢。
如果我
错了,请原谅我,但不应该AND b.meta_value >= {$this->age_max}
吗?
你不需要
转换类型我认为错误是一致的 AND a.meta_value <= {$this->age_min}
不应该是AND a.meta_value >= {$this->age_min}
吗?