每个左连接返回 1 个结果
目前我正在两个表上执行左连接。第一个表有一个 id 和一个人名,第二个表有一个 id,即表 1 中人员的 id,然后是时间戳(他们最后一次乘坐的航班的时间戳)。
People Flights
id | name id | person_id | time
------------ ---------------------------
1 Dave 1 1 1284762115
2 Becky 2 1 1284787352
3 2 1284772629
4 2 1286432934
5 1 1289239480
当我执行左连接时,我会得到人员及其飞行时间的列表,但我想要的只是人员及其上次飞行时间的列表。
所以 SELECT p.id, p.name, f.time FROM People p LEFT JOIN Flights f ON p.id = f.person_id
返回
1 Dave 1284762115
1 Dave 1284787352
1 Dave 1289239480
2 Becky 1284772629
2 Becky 1286432934
我只想看到:
1 Dave 1289239480
2 Becky 1286432934
所以我只需要返回具有最高 f.id 的匹配或最高 f.time
Currently I am performing a left join on two tables. The first table has an id and a persons name, the second table has an id, the id of a person from table 1, and then a timestamp (of the last flight they had).
People Flights
id | name id | person_id | time
------------ ---------------------------
1 Dave 1 1 1284762115
2 Becky 2 1 1284787352
3 2 1284772629
4 2 1286432934
5 1 1289239480
When I perform my left join, I get a list of people and their flight times, but what I would like is just the list of people with their last flight times.
So SELECT p.id, p.name, f.time FROM People p LEFT JOIN Flights f ON p.id = f.person_id
Returns
1 Dave 1284762115
1 Dave 1284787352
1 Dave 1289239480
2 Becky 1284772629
2 Becky 1286432934
I would like to see just:
1 Dave 1289239480
2 Becky 1286432934
So I need to return only the match with the highest f.id or the highest f.time
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
试试这个:
Try this: