对结果进行分组的 MySQL 语句，但也返回组内另一列的最低和最高值 [英] MySQL statement that groups results, but also returns the lowest and the highest value of another column from within the group

问题描述

好的，这是我的假设表:

Ok, so this is my hypothetical table:

Id     Page       Timestamp     Etc

 1        1      2009-10-10     ...
 2        1      2009-10-13     ...
 3        1      2009-10-14     ...
 4        1      2009-10-20     ...
 5        2      2009-10-24     ...
 6        2      2009-10-27     ...
 7        2      2009-11-06     ...
 8        3      2009-11-06     ...
 9        4      2009-11-07     ...
10        4      2009-11-20     ...
11        4      2009-11-21     ...

我需要的是一个查询，在每一行中返回页码...

And what I need is a query that in each row returns the page number...

SELECT * FROM `hypothetical_table` WHERE `Visible` = 1 AND `foo` = 'blargh' GROUP BY `Page` HAVING COUNT(`Page`) >= 1 ORDER BY `Page` ASC   

...但还返回组内时间戳列的最低和最高值.因此，另一方面，我可以输出如下内容:

... but also returns the lowest and the highest value from the Timestamp column within the group. So that on the flipside I can output something like:

• 第 1 页(从 2009 年 10 月 10 日到 2009 年 10 月 20 日)
• 第 2 页(从 2009 年 10 月 24 日到 2009 年 11 月 6 日)
• 第 3 页(2009 年 11 月 6 日)
• 第 4 页(从 2009 年 11 月 7 日到 2009 年 11 月 21 日)

• Page 1 (from October 10th 2009 to October 20th 2009)
• Page 2 (from October 24th 2009 to November 6th 2009)
• Page 3 (November 6th 2009)
• Page 4 (from November 7th 2009 to November 21th 2009)

这甚至可以用直接的 MySQL 来实现吗?

Is this even doable with straight MySQL?

推荐答案

怎么样:

SELECT `Page`, MIN(`Timestamp`) as StartDate, MAX(`Timestamp`) as EndDate
    FROM `hypothetical_table` 
    WHERE `Visible` = 1 
        AND `foo` = 'blargh' 
    GROUP BY `Page` 
    HAVING COUNT(`Page`) >= 1 
    ORDER BY `Page` ASC 

这篇关于对结果进行分组的 MySQL 语句，但也返回组内另一列的最低和最高值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！