SQL：仅返回第一次出现
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8729333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Return only first occurrence
提问by Nime Cloud
I seldomly use SQL and I cannot find anything similar in my archive so I'm asking this simple query question: I need a query which one returns personIDand only the first seenTime
我很少使用 SQL，我在我的档案中找不到任何类似的东西，所以我问这个简单的查询问题：我需要一个查询，其中一个返回personID并且只返回第一个seenTime
Records:
记录：
seenID | personID | seenTime
108 3 13:34
109 2 13:56
110 3 14:22
111 3 14:31
112 4 15:04
113 2 15:52
Wanted result:
想要的结果：
personID | seenTime
3 13:34
2 13:56
4 15:04
That's what I did & failed:
这就是我所做的并失败了：
SELECT t.attendanceID, t.seenPersonID, t.seenTime
(SELECT ROW_NUMBER() OVER (PARTITION BY seenID ORDER BY seenID) AS RowNo,
seenID,
seenPersonID,
seenTime
FROM personAttendances) t
WHERE t.RowNo=1
P.S: Notice SQL CE 4
PS：注意 SQL CE 4
回答by Tim Lehner
If your seenTime increases as seenID increases:
如果您的 seenTime 随着 seenID 的增加而增加：
select personID, min(seenTime) as seenTime
from personAttendances
group by personID
Update for another case:
更新另一个案例：
If this is not the case, and you really want the seenTime that corresponds with the minimum seenID (assuming seenID is unique):
如果情况并非如此，并且您确实想要与最小 seenID 对应的 seenTime（假设 seenID 是唯一的）：
select a.personID, a.seenTime
from personAttendances as a
join (
-- Get the min seenID for each personID
select personID, min(seenID) as seenID
from personAttendances
group by personID
) as b on a.personID = b.personID
where a.seenID = b.seenID
回答by Joe
You're making it waytoo difficult:
你正在做的方式太困难：
select personID, min(seenTime)
from personAttendances
group by personID
回答by gilad mayani
for PostgreSQLthere is DISTINCT ON
回答by Petar Ivanov
You need to order by seen time not by seen id:
您需要按看到的时间而不是看到的 id 订购：
PARTITION BY seenID ORDER BY seenTime
回答by jerry
Add this to your SQL:
将此添加到您的 SQL：
and where not exists
(select 1 from personAttendances t2
where t.personID=t2.personID
and t2.seenID < t.seenID)
