mysql - SQL 顺序转换

Question

我在通过将长文本 (b.meta_value) 转换为日期时间来排序查询时遇到问题。我收到错误 1064。我的数据库中的格式是 mm/dd/yyyy，我尝试 order by，但我一直收到错误 1064。问题出在哪里？:

convert(convert(b.meta_value,char(30)),DATETIME, 101)
convert(DATETIME, convert(b.meta_value,varchar(30)), 101)
convert(DATETIME, STR_TO_DATE(b.meta_value, '%m/%d/%Y'), 101)

这是我的查询:

SELECT 
d.name as name,
b.post_id as post_id,
b.meta_value as meta_value 
FROM wp_posts a 
inner join wp_postmeta b 
on a.id=b.post_id 
inner join wp_term_relationships c 
on c.object_id=b.post_id 
inner join wp_terms d 
on c.term_taxonomy_id=d.term_id 
where meta_key in('px_event_from_date') 
  and a.post_type='events' 
  and a.post_status!='trash' 
  and d.term_id = 104 
  and STR_TO_DATE(b.meta_value, '%m/%d/%Y') <= CURDATE() 
order by convert(DATETIME, STR_TO_DATE(b.meta_value, '%m/%d/%Y')) desc 
limit 0,1

Answer 1

你应该在你的 JOIN 中设置 meta_key 过滤器，比如 meta_key = 'my_date' (因为我不知道你使用哪个元键正试图捕获):

SELECT 
d.name as name,
b.post_id as post_id,
b.meta_value as meta_value 
FROM wp_posts a 
inner join (
  SELECT 
     post_id,
     meta_value
  FROM wp_postmeta 
  WHERE meta_key = 'my_date'
) b 
on a.id=b.post_id 
  and  STR_TO_DATE(b.meta_value, '%m/%d/%Y') <= CURDATE() 
inner join wp_term_relationships c 
on c.object_id=b.post_id 
inner join wp_terms d 
on c.term_taxonomy_id=d.term_id 
where meta_key in('px_event_from_date') 
  and a.post_type='events' 
  and a.post_status!='trash' 
  and d.term_id = 104 
order by STR_TO_DATE(b.meta_value, '%m/%d/%Y') desc 
limit 0,1