mysql - ORDER BY MySQL中的相等值

Question

全部，到目前为止，我有以下 SQL 查询:

SELECT * FROM $wpdb->posts
JOIN $wpdb->term_relationships ON $wpdb->term_relationships.object_id=$wpdb->posts.ID
JOIN $wpdb->postmeta ON $wpdb->postmeta.post_id=$wpdb->posts.ID
WHERE 
$wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'post'
AND $wpdb->term_relationships.term_taxonomy_id='$vendor_category_to_use'
AND $wpdb->postmeta.meta_key='zip'

我有一个名为 featured 的 postmeta.meta_key。然后，该帖子是否具有特色的值位于另一个名为 postmeta.meta_value 的列中。如果 meta_value = "yes"，其中 meta_key 等于“featured”，那么我想先显示这个，然后再显示其余的帖子。我该怎么做呢？

编辑:这是数据设置:

postid       meta_key      meta_value
123          featured      yes
324          featured      no
182          featured      yes
873          featured      yes

所以在这个例子中，我希望我的帖子按以下顺序显示:

postid       meta_key      meta_value
123          featured      yes
182          featured      yes
873          featured      yes
324          featured      no

希望对您有所帮助!

这是生成的 SQL:

SELECT * 
FROM wp_posts 
JOIN wp_term_relationships ON wp_term_relationships.object_id=wp_posts.ID 
JOIN wp_postmeta ON wp_postmeta.post_id=wp_posts.ID 
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' AND wp_term_relationships.term_taxonomy_id='5' AND wp_postmeta.meta_key='zip' AND (wp_postmeta.meta_value = '46320' OR wp_postmeta.meta_value = '46321' OR wp_postmeta.meta_value = '46322' OR wp_postmeta.meta_value = '46323' OR wp_postmeta.meta_value = '46324' OR wp_postmeta.meta_value = '46327' OR wp_postmeta.meta_value = '46394' OR wp_postmeta.meta_value = '46402' OR wp_postmeta.meta_value = '46404' ) 
ORDER BY (wp_postmeta.meta_key='featured' AND wp_postmeta.meta_value='yes') DESC, wp_posts.post_date DESC

谢谢!

Answer 1

SELECT ...
FROM   ...
ORDER BY (meta_key='featured' AND meta_value='yes') DESC, postid ASC;

如果 (meta_key='featured' AND meta_value='yes') 对于某行，该行的值为 1/TRUE。否则，它将有一个 0/​​FALSE。因此，降序排序会将具有 TRUE 的行放在最前面。