在SQL Join中选择第二个表的第一行 [英] Select Top row of 2nd table in SQL Join

问题描述

我必须创建名为USERS和COMM_HISTORY

USERS(user_id, name)
COMM_HISTORY(comm_history_id, comm_date, comm_by, user_id)

USERS表将具有唯一的用户列表，并且COMM_HISTORY可以具有user_id的重复项.当我加入他们时，我想从COMM_HISTORY获取用户名和最新的comm_date.

USERS table will have a unique list of users and COMM_HISTORY can have duplicates of user_id. When I join them I would like to grab the name of user and the most recent comm_date from COMM_HISTORY.

与此帖子类似的内容 sql join-仅选择top第二张表的第二行，但该帖子似乎没有正确的答案.

Something similar with this post sql join - only select top row from 2nd table but it seems there's no correct answers in that post.

我想加入他们时得到ff结果

I would like to have the ff result when I join them

1 John Doe 2012-01-29 Jane Doe

代替

1 John Doe 2011-10-20 Jane Lee
2 John Doe 2012-01-29 Jane Doe
3 John Doe 2011-09-08 Jane Doe

有人对此有解决方案吗?

Anyone has a solution for this?

推荐答案

@Andomar在另一个问题中的解决方案应该起作用.适应您的用例，它将是:

@Andomar's solution in the other question should work. Adapted to your use case, it would be:

SELECT USERS.*, COMM_HISTORY.*
FROM USERS
INNER JOIN COMM_HISTORY
  ON USERS.user_id = COMM_HISTORY.user_id 
LEFT JOIN COMM_HISTORY LATER_HISTORY
  ON COMM_HISTORY.user_id = LATER_HISTORY.user_id 
  AND LATER_HISTORY.comm_date > COMM_HISTORY.comm_date
WHERE LATER_HISTORY.user_id IS NULL

将历史记录表重新加入自身，并设置一个条件，表明以后不应再选择任何历史记录行.

Join the history table back onto itself, and set a condition saying no later history rows should be selected.

这篇关于在SQL Join中选择第二个表的第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！