sql join - 仅从第二个表中选择顶行

发布于 2024-08-13 10:16:10 字数 445 浏览 2 评论 0原文

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

我想从表 b 中为表 a 中的每个客户代码/电话号码选择最近的呼叫。

到目前为止，我已经：

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

但这是显示 TableB 中的所有结果。我真的只是想接到最近的电话，如果这有意义的话？

原文

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

I want to select the most recent call from table b for each of the customercodes/phone numbers in table 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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半枫 2024-08-20 10:16:10

您可以将调用表自身连接起来，并指定后面的行不能存在。例如：

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 is null 表示不能有后面的行。如果有多行具有相同的 StartTime，这将选择所有行。

这允许您选择最新调用中的所有列。

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

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.

回复收藏 0 原文

无声情话 2024-08-20 10:16:10

如果您只想要表 b 中的开始时间，您可以使用 group by

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

If you only want the starttime from table b, you could use a group by

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

回复收藏 0 原文

别挽留 2024-08-20 10:16:10

像这样的东西吗？

SELECT A.CustomerCode, A.PhoneNumber, Max(B.StartTime) AS MaxOfStartTime
FROM A INNER JOIN B ON A.PhoneNumber = B.PhoneNumber
GROUP BY A.CustomerCode, A.PhoneNumber;

Something like this?

SELECT A.CustomerCode, A.PhoneNumber, Max(B.StartTime) AS MaxOfStartTime
FROM A INNER JOIN B ON A.PhoneNumber = B.PhoneNumber
GROUP BY A.CustomerCode, A.PhoneNumber;

回复收藏 0 原文

不疑不惑不回忆 2024-08-20 10:16:10

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

我相信这会给出您想要的结果，前提是 B.StartTime 在与表 A 中的每一行相关的集合中唯一。如果您处理的 DateTime 和事件不太接近，那么这应该可靠。

我知道的唯一其他方法是在 MSSQL 2005+ 中使用 CROSS APPLY

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

I believe this will give your desired result, provided B.StartTime is unique in the set related to each row from table A. If you're dealing with a DateTime and events not too close together, this should be reliable.

The only other way I know is using CROSS APPLY in MSSQL 2005+

回复收藏 0 原文

~没有更多了~