MySQL - 如何将LIMIT应用于GROUP？ [英] MySQL - How to apply LIMIT to GROUP?

问题描述

我创建了一种博客引擎，根据时间戳将帖子分为月份类别。我使用以下查询：

pre $ SELECT MONTHNAME（post_time）AS
month，YEAR（post_time）AS year
FROM blog_posts
GROUP BY年，
月
ORDER BY post_time DESC
极限0，10


它给我回到了最新的十个类别：

  December 2010 
 2010年11月
 ... 
等
  

我的问题是如果我有一个像* category_id *的列，我可以在LIMIT中轻松使用它，
但作为月份和年份是我所有我卡住了。

非常感谢，这是我的一个谜。

我的问题是如何创建一个查询来获取较旧/较新的类别？
blockquote>

此查询：

  SELECT MONTHNAME（post_time）AS month，YEAR（ post_tim e）AS年
从blog_posts 
 GROUP BY 
年，月
 ORDER BY 
 post_time DESC 
限额0,10 
  

按月份和年份分组，以及每个月和每年的随机邮政订单。

由于这些随机帖子的顺序与月份和年份的顺序相对应，因此您的类别将按照正确的顺序排列（最近到最早）。

更新：

在 2010年6月之前显示 10 ：

  SELECT MONTHNAME（post_time）AS月，年（post_time）AS年
 FROM blog_posts 
 WHERE post_time< '2010-06-01'
 GROUP BY 
年，月
 ORDER BY 
 post_time DESC 
限制0,10 
  

I create a kind of a blog engine, where posts are categorized into month-year categories, based on timestamp. I use the following query:

SELECT MONTHNAME( post_time ) AS
month , YEAR( post_time ) AS year
FROM blog_posts
GROUP BY year,
month
ORDER BY post_time DESC
LIMIT 0 , 10

which returns me the latest ten post categories like this:

December 2010
November 2010
...
etc

My question is how do I create a query to fetch older/newer categories?

If I had a column like *category_id* I'd be able to easily use it with LIMIT, but as month and year is all I have I'm stuck.

Help greatly appreciated, this one's a riddle for me.

解决方案

My question is how do I create a query to fetch older/newer categories?

This query:

SELECT  MONTHNAME(post_time) AS month, YEAR(post_time) AS year
FROM    blog_posts
GROUP BY
        year, month
ORDER BY
        post_time DESC
LIMIT 0, 10

groups by months and years and orders by time of the random post within each month and year.

Since the order of these random posts correspond to that of months and years, your categories will go out in correct order (recent to early).

Update:

To show 10 categories before June 2010:

SELECT  MONTHNAME(post_time) AS month, YEAR(post_time) AS year
FROM    blog_posts
WHERE   post_time < '2010-06-01'
GROUP BY
        year, month
ORDER BY
        post_time DESC
LIMIT 0, 10

这篇关于MySQL - 如何将LIMIT应用于GROUP？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！