MySQL Group By显示最新结果 [英] MySQL Group By to display latest result
问题描述
我正在尝试查询MySQL以进行ORDER然后是GROUP ...这是一个经常出现的问题,我找到了一个与我相关的答案:
I'm trying to query MySQL to ORDER then GROUP... it's a question that comes up a lot here and I found an answer that seemed relevant to me: Getting a MySQL group by query to display the row in that group with the highest value
但是我发现在进行分组之前,它仍然没有订购.
However I'm finding that it is still not ordering before doing the grouping.
具体地说,我要使用的是Wordpress自定义帖子类型,以按发布日期排序的名为日期"的元数据字段分组.
Specifically what I'm trying to do is use Wordpress custom post types to group by a meta data field called 'date', ordered by the post date.
这是我的查询:
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
期望的结果是每个日期"(这是一个meta字段)的帖子,以及根据post_date的日期"的最新帖子.
The desired results are a post for each 'date' (this is a meta field), with the latest post for this 'date' as per the 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
)
这篇关于MySQL Group By显示最新结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!