用于按列选择系列中第一个的 SQL 查询

Question

我在想出我正在尝试做的事情的查询时遇到了一些麻烦。

我有一个我们称之为“运动”的表格，其中包含以下列:

RecID(Key), Element(f-key), Time(datetime), Room(int)

该表包含元素运动的历史记录。一条记录包含记录所针对的元素、记录位置的时间以及当时所在的房间。

我要的是一个Element进入房间的所有记录。这意味着同一房间内该元素的一系列移动中的任何元素的第一个(按时间)条目。

输入是房间号和时间。 IE，我想要所有表明任何元素在时间 Y 之后进入房间 X 的记录。

我最接近的是这个

Select Element, min(Time)
from Movements
where Time > Y and Room = x
group by Element

虽然这只会给我每个元素一个房间进入记录(如果元素从时间 Y 开始进入房间 X 两次，我只会得到第一个)有什么想法吗？如果我没有解释清楚，请告诉我。

我正在使用 MS SQLServer 2005。

Answer 1

这听起来像是典型的每组最小值，在 SQL Server 中可以使用 ROW_NUMBER 来解决:

SELECT Element, Time
FROM (
    SELECT
        Element, Time, 
        ROW_NUMBER() OVER(PARTITION BY Element ORDER BY Time) AS rn
    FROM Movements
    WHERE Room = 1 AND Time > '2010-06-01 00:30:00'
) T1
WHERE rn = 1

示例结果:

Element  Time                   1        2010-06-01 02:00:00.0002        2010-06-01 03:00:00.000

Test data:

CREATE TABLE Movements (RecID INT NOT NULL, Element INT NOT NULL, Time DATETIME NOT NULL, Room INT NOT NULL);
INSERT INTO Movements (RecID, Element, Time, Room) VALUES
(1, 1, '2010-06-01 00:00:00', 1),
(2, 1, '2010-06-01 01:00:00', 2),
(3, 1, '2010-06-01 02:00:00', 1),
(4, 2, '2010-06-01 03:00:00', 1),
(5, 2, '2010-06-01 04:00:00', 2),
(6, 1, '2010-06-01 05:00:00', 3),
(7, 2, '2010-06-01 06:00:00', 2);