SQL min / max分组问题

I have something like the following data structure:

我有类似以下数据结构:

Category    StartDateTime       EndDateTime
===============================================
1           12/1/2009 12:00     12/1/2009 12:12
1           12/1/2009 04:00     12/1/2009 04:20
2           12/2/2009 10:15     12/2/2009 10:22
2           12/2/2009 11:00     12/2/2009 11:01

I want the min StartDateTime and max EndDateTime for each Category. Like this:

我想要每个类别的最小StartDateTime和最大EndDateTime。像这样:

Category    MinStartDateTime    MaxEndDateTime
===============================================
1           12/1/2009 12:00     12/1/2009 04:20
2           12/2/2009 10:15     12/2/2009 11:01

Using min & max with a group by Category doesn't seem to work:

使用按类别分组的最小值和最大值似乎不起作用:

select
    Category,
    min(StartDateTime) [MinStartDateTime],
    max(EndDateTime) [MaxDateTime]
from
    MyTable
group by
    Category
order by
    Category,
    StartDateTime,
    EndDateTime

I also tried two inner joins on a sub-query for each min and max statement, however it seems to be excluding some records:

我还在每个min和max语句的子查询中尝试了两个内部联接,但它似乎排除了一些记录:

select distinct
    T1.Category,
    T1.StartDateTime [MinStartDateTime],
    T1.EndDateTime [MaxEndDateTime]

from
    MyTable T1

inner join
    (select
        Category,
        min(StartDateTime) [MinStartDateTime]
     from
        MyTable
     group by
        Category) T2
on T2.Category = T1.Category and T2.MinStartDateTime = T1.StartDateTime

inner join
    (select
        Category,
        max(EndDateTime) [MaxEndDateTime]
     from
        MyTable
     group by
        Category) T3
on T3.Category = T1.Category and T3.MaxEndDateTime = T1.EndDateTime

order by
    T1.Category,
    T1.encodeStartDateTime,
    T1.encodeEndDateTime

Any ideas? The database is Sybase ASE which should be SQL-92 compliant.

有任何想法吗?数据库是Sybase ASE,应该符合SQL-92。

1 个解决方案

#1

Your first solution looks correct except for the order by clause; try:

您的第一个解决方案看起来正确,除了order by子句;尝试:

select
    Category,
    min(StartDateTime) [MinStartDateTime],
    max(EndDateTime) [MaxDateTime]
from MyTable
group by
    Category
order by
    Category,
    MinStartDateTime,
    MaxDateTime