从多个字段中选择max/min值

In the following query the start/finish columns are datetime fields.

在下面的查询中，开始/结束列是datetime字段。

How should I modify this query to get two more columns, one with the min date and one with the max date (of all the 6 datetime fields and all the rows) repeated in each row.

我应该如何修改这个查询，以便在每一行中重复两个列，一个列具有最小日期，另一个列具有最大日期(所有6个datetime字段和所有行中的最大日期)。

Alternatively how could I create a new query returning only these 2 (min/max) dates, for the same resultset of course?

或者，我如何创建一个新的查询，只返回这两个(最小/最大)日期，当然是相同的resultset ?

Thanks a lot! (I would like answers for both SQL Server 2005 and Sybase ASE 12.5.4)

谢谢!(我想知道SQL Server 2005和Sybase ASE 12.5.4的答案)

select  erg_mst.code, 
        erg_types.perigrafh, 
        erg_mst.FirstBaseStart, 
        erg_mst.FirstBaseFinish,
        erg_mst.LastBaseStart, 
        erg_mst.LastBaseFinish ,
        erg_mst.ActualStart, 
        erg_mst.ActualFinish 
from    erg_mst inner join 
        erg_types  on erg_mst.type = erg_types.type_code  
where   erg_mst.activemodule = 'co' 
and     (
            FirstBaseStart <> NULL OR 
            FirstBaseFinish <> NULL OR  
            LastBaseStart <> NULL OR 
            LastBaseFinish <> NULL OR 
            ActualStart <> NULL OR 
            ActualFinish <> NULL 
        )  
order by    isnull(FirstBaseStart,isnull(LastBaseStart,ActualStart))

2 个解决方案

#1

See below for a SQL Server 2005 code sample using Miles D's suggestion of using a series of UNION'ed selects (sorry, I don't know Sybase syntax):

请参阅下面的SQL Server 2005代码示例，使用Miles D的建议使用一系列UNION'ed select(对不起，我不知道Sybase语法):

select min(AllDates) as MinDate, max(AllDates) as MaxDate
from
(
select erg_mst.FirstBaseStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     FirstBaseStart IS NOT NULL
union all
select erg_mst.FirstBaseFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     FirstBaseFinish IS NOT NULL
union all
select erg_mst.LastBaseStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     LastBaseStart IS NOT NULL
union all
select erg_mst.LastBaseFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     LastBaseFinish IS NOT NULL
union all
select erg_mst.ActualStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     ActualStart IS NOT NULL
union all
select erg_mst.ActualFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     ActualFinish IS NOT NULL
) #Temp

#2

I can think of two solutions, but both will need to take on board Lucer's comment to use IS NOT NULL, rather than <> NULL.

我可以想出两个解决方案，但这两个方案都需要考虑Lucer的注释来使用，而不是<> NULL。

Create a two user defined function to return the max and minimum values - ok, but assumes you have access to do it.
创建一个两个用户定义的函数来返回最大值和最小值——好的，但是假设您可以访问它。
Use a series of UNION'ed selects, each one selecting one of the six columns and then use this as the inner nested SELECT where you then use SELECT MAX(), MIN() from that.
使用一系列的UNION'ed SELECT，每个SELECT都选择六列中的一列，然后使用这个作为内部嵌套的SELECT，然后在其中使用SELECT MAX()、MIN()。

#1