在MySQL中按相等值排序 [英] ORDER BY an equal value in MySQL
问题描述
全部, 到目前为止,我已经获得了以下SQL查询:
All, I've got the following SQL query as of now:
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等于功能",那么我想先显示一个,然后再显示其余的帖子.我该怎么做呢?
I have a postmeta.meta_key
called featured
. Then the value of if the post is featured is in another column called postmeta.meta_value
. If the meta_value = "yes"
where the meta_key equals "featured" then I want to display this one first and then display the rest of the posts after that. How can I go about doing that?
这是数据设置:
postid meta_key meta_value
123 featured yes
324 featured no
182 featured yes
873 featured yes
因此在此示例中,我希望我的帖子按此顺序显示:
So in this example I'd like my posts to be displayed in this order:
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
谢谢!
推荐答案
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的行排在首位.
If (meta_key='featured' AND meta_value='yes')
for a row, that row will have a 1/TRUE. Otherwise, it will have a 0/FALSE. Hence, sorting descending puts the rows that have TRUE first.
这篇关于在MySQL中按相等值排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!