mysql结果group,MySQL Group By显示最新结果

但是我发现在进行分组之前它仍然没有订购.

具体来说,我要做的是使用Wordpress自定义帖子类型按名称为“日期”的元数据字段进行分组,按发布日期排序.

这是我的查询：

SELECT

`ID`, `date`, `post_date`, `date_rank`

FROM (

SELECT

`Post`.`ID`,

`Post`.`post_date`,

`PostData`.`meta_value` AS `date`,

CASE

WHEN @data_date = `PostData`.`meta_value` THEN @rowum := @rownum + 1

ELSE @rownum := 1

END AS date_rank,

@data_date := `PostData`.`meta_value`

FROM

`".$this->wpdb->posts."` AS `Post`

JOIN

`".$this->wpdb->postmeta."` AS `PostData`

ON `Post`.`ID` = `PostData`.`post_id` AND `PostData`.`meta_key` = 'date'

JOIN (SELECT @rownum := 0, @data_date := '') AS `vars`

WHERE

`Post`.`post_type` = 'my_post_type'

AND

`Post`.`post_status` = 'publish'

ORDER BY `Post`.`post_date` DESC

) AS `x`

WHERE date_rank = 1

ORDER BY `date` ASC

所需的结果是每个“日期”的帖子(这是一个元字段),根据post_date显示此“日期”的最新帖子.

解决方法:

SELECT *

FROM (

SELECT DISTINCT meta_value

FROM postdata pd

WHERE pd.meta_key = 'date'

) pd

JOIN post p

ON p.id =

(

SELECT post_id

FROM postdata pdi

JOIN post pi

ON pi.id = pdi.post_id

WHERE pdi.meta_key = 'date'

AND pdi.meta_value = pd.meta_value

ORDER BY

pi.post_date DESC, pi.id DESC

LIMIT 1

)

标签：custom-post-type,mysql,wordpress,greatest-n-per-group

来源： https://codeday.me/bug/20191001/1840138.html