Wordpress mysql group by |订购 [英] Wordpress mysql group by | order by
本文介绍了Wordpress mysql group by |订购的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
$post = $wpdb->get_results("SELECT `p`.`ID`, MAX(p.post_date) as `datetime`, `p`.`post_date`, `p`.`post_content`, `p`.`post_title`, `p`.`post_status`, `p`.`post_name`, `p`.`comment_count`, `tax`.`term_taxonomy_id`, `tax`.`term_id`, `tax`.`taxonomy`, `tax`.`parent`, `rel`.`object_id`, `rel`.`term_taxonomy_id`, `t`.`term_id`, `t`.`name`, `t`.`slug`
FROM (`$wpdb->posts` AS p)
INNER JOIN `$wpdb->term_relationships` AS rel ON `rel`.`object_id` = `p`.`ID`
INNER JOIN `$wpdb->term_taxonomy` AS tax ON `tax`.`term_taxonomy_id` = `rel`.`term_taxonomy_id`
INNER JOIN `$wpdb->terms` AS t ON `t`.`term_id` = `tax`.`term_id`
WHERE `tax`.`taxonomy` = 'category'
AND `p`.`post_status` = 'publish'
AND `p`.`post_type` = 'post'
GROUP BY tax.parent
ORDER BY datetime DESC
LIMIT 4");
我需要找到每个类别的最新帖子,然后对结果进行分组,以便每个类别只有一个最新帖子.
I need to find the latest post for each category and then group the results so I only a single latest post for each category.
我使用;GROUP BY tax.parent
不起作用;ORDER BY datetime DESC
推荐答案
您的 ID 是递增的.用那个.
your IDs are incremental. use that.
select ...
from
.....
where id post_in
(select max(post_id) from table group by category)
如果你知道有多少个类别,你可以使用
if you know how many categories there are, you can do it even better using
where post id in
(select post_id from table where category=1 order by time desc limit 1
union
select post_id from table where category=2 order by time desc limit 1
union
select post_id from table where category=3 order by time desc limit 1
union
select post_id from table where category=4 order by time desc limit 1)
或者你可以使用参数,这些参数会给你一个完美的结果,但查询速度很慢
or you could use parameters which will give you a perfect result but a very slow query
select * from
(select
@rn:=if(@prv=category_id, @rn+1, 1) as rId,
@prv:=category_id as category_id,
timestamp,
other columns
from (select category_id, timestamp, other columns from ... )a
join
(select @prv:=0, @rn:=0)tmp
order by
category_id , timestamp desc) a
where rid<=1
这篇关于Wordpress mysql group by |订购的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文