检索 SQL 中的最新活动

Question

美好的一天

我有一个在不同日期发生的活动表，可以使用下面的查询 (T-SQL( 进行检索：

Select  Activities.activityid,
        Activities.ActivityTypeCode,
        Activities.ActivityDate,
        Activities.SequenceOrder,
        Activities.Place,
        Activities.TypeOfPlace
From Activities
Where Activities.ActivityTypeCode In ('LOST', 'FOUND', 'NOT SEEN')

Queston：如何检索最新的？还是第二个最新的？

供您参考：有一个名为 sequenceOrder 的某个字段，用于跟踪输入活动时的序列。

请帮忙。

Answer 1

将GROUP BY子句与子查询一起使用

SELECT * 
FROM Activities
WHERE sequenceOrder IN (
              SELECT MAX(SequenceOrder) 
              FROM Activities
              WHERE ActivityTypeCode In ('LOST', 'FOUND', 'NOT SEEN')
              GROUP BY ActivityTypeCode 
)

如果您想获得其中一个(丢失、已找到、未看到(中唯一最活跃的活动，则从子查询中删除GROUP BY子句。

Answer 2

select * from (
Select  Activities.activityid,
    Activities.ActivityTypeCode,
    Activities.ActivityDate,
    Activities.SequenceOrder,
    Activities.Place,
    Activities.TypeOfPlace,
    row_number() over (partition by Activities.ActivityTypeCode order by Activities.SequenceOrder) [rn]
From Activities
Where Activities.ActivityTypeCode In ('LOST', 'FOUND', 'NOT SEEN')
) a where rn = 1 --2 if you want second most recent

Answer 3

使用 ROW_NUMBER ：

SELECT
    activityid,
    ActivityTypeCode,
    ActivityDate,
    SequenceOrder,
    Place,
    TypeOfPlace
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY SequenceOrder DESC) rn
    FROM Activities
    WHERE ActivityTypeCode IN ('LOST', 'FOUND', 'NOT SEEN')
) t
WHERE rn = 1;

这种方法的优点是，如果您想要第二个最近的事件，我们可以使用 WHERE rn = 2 .

请注意，如果序列顺序中的值可能存在联系，那么我们可能希望使用排名函数而不是行号。

您可以将上述查询放入 CTE(公用表表达式(中：

WITH cte AS (
    SELECT
        activityid,
        ActivityTypeCode,
        ActivityDate,
        SequenceOrder,
        Place,
        TypeOfPlace
    FROM
    (
        SELECT *,
            ROW_NUMBER() OVER (ORDER BY SequenceOrder DESC) rn
        FROM Activities
        WHERE ActivityTypeCode IN ('LOST', 'FOUND', 'NOT SEEN')
    ) t
    WHERE rn = 1
)
SELECT * FROM cte;

但请注意，从性能的角度来看，将查询包装在子查询中是不可取的。 相反，只需定制我的原始查询以满足您的需求。