sql join-仅从第二个表中选择第一行 [英] sql join - only select top row from 2nd table

问题描述

一个sql noob的位，在表a中有一个客户代码/电话号码的列表，表b中有所有的通话记录。

Bit of an sql noob, have a list in table a of customercodes/phone numbers, and table b has all the call records.

我要选择

到目前为止，我已经从表b中为表a中的每个客户代码/电话号码打电话。

So far I have:

SELECT     A.CustomerCode, A.PhoneNumber, B.StartTime
FROM         tableA A INNER JOIN
             tableB B ON ( A.PhoneNumber = B.PhoneNumber 
                           AND A.CustomerCode = B.CustomerCode )
ORDER BY A.CustomerCode, A.CLI, B.StartTime DESC

$ b $订购b

但是，这调出了TableB的所有结果。我真的只是想获得最近的通话（如果有意义）？

But that is bringing up all the results from TableB. I literally just want to get the most recent call if that makes sense?

推荐答案

您可以自行加入calls表，并且指定以后可能不存在任何行。像这样：

You can join the calls table on itself, and specify that no later row may exist. Like:

SELECT       A.CustomerCode, A.PhoneNumber, B.StartTime
FROM         tableA A 
INNER JOIN   tableB B 
ON           A.PhoneNumber = B.PhoneNumber 
AND          A.CustomerCode = B.CustomerCode
LEFT JOIN    tableB later
ON           B.PhoneNumber = later.PhoneNumber 
AND          B.CustomerCode = later.CustomerCode
AND          later.StartTime > B.StartTime
WHERE        later.PhoneNumber is null

条件 later.PhoneNumber为null 表示以后不能再行了。如果有多个具有相同StartTime的行，则将全部选中。

The condition later.PhoneNumber is null says there can't be a later row. If there are multiple rows with the same StartTime, this will select all of them.

这可让您从最近一次调用中选择所有列。

This allows you to select all columns from the latest call.

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