在SQL Server中选择一组日期

我有一个历史表来捕获某个对象的更新,除了其他信息之外,还会捕获此更新发生的时间.我想这样做是SELECT在MIN(LogDate)对应于某一ActionTaken列.

更具体地说,历史表可能有其他(更近期)行ActionTaken = 1,但我想捕获日期ActionTaken 变为 1.

例:

SELECT  MIN(LogDate) AS FirstActionDate
FROM    HistoryTable
WHERE   ID = 123
    AND FirstActionTaken = 1

SELECT  MIN(LogDate) AS SecondActionDate
FROM    HistoryTable
WHERE   ID = 123
    AND SecondActionTaken = 1

SELECT  MIN(LogDate) AS ThirdActionDate
FROM    HistoryTable
WHERE   ID = 123
    AND ThirdActionTaken = 1

这很好用,我收到了正确的日期没有问题.当我遇到麻烦,然后要select对MAX(LogDate)从该组:

SELECT  MAX(LogDate) AS LastActionDate
FROM    HistoryTable
WHERE   ID = 123
    AND LogDate IN 
    (
            (   SELECT  MIN(LogDate) AS FirstActionDate
                FROM    HistoryTable
                WHERE   ID = 123
                    AND FirstActionTaken = 1    ),

            (   SELECT  MIN(LogDate) AS SecondActionDate
                FROM    HistoryTable
                WHERE   ID = 123
                    AND SecondActionTaken = 1   ),

            (   SELECT  MIN(LogDate) AS ThirdActionDate
                FROM    HistoryTable
                WHERE   ID = 123
                    AND ThirdActionTaken = 1    )
    )

这也有效,但我讨厌这样做.我可以把以前的陈述保存到变量中,而不仅仅是SELECT MAX()那些变量; 它肯定会更具可读性,但JOIN这个查询的语法是什么样的？

有没有办法将前三个SELECT语句合并为一个返回所有三个日期并且不是一个难以理解的混乱？

如何LogDate从此结果集中获取最新的(作为单独的列)并且没有(看似不必要的)重复SELECT语句？

编辑:

以下是我发现的与迄今为止给出的答案相关的一些链接:

数据规范化

运用 OUTER/CROSS APPLY

UNPIVOT (和别的)

希望这些能够帮助其他人寻找类似问题的解决方案!