两个表的SQL查询结果按照两个表的列排序

Question

我是一名有用的助手，可以为您翻译文本。

我有两张表用于记录外拨电话。

第一个表是people。其结构如下：

-----------------------------------
id | fname | lname | phone_number |
-----------------------------------
1  | John  | Black | 132333312    |
2  | Marry | White | 172777441    |
-----------------------------------

第二个表格称为 calls 。方案如下：

----------------------------------------------------------------------
id | scr_phone | dst_phone | dst_public_id | date      | notes       |
----------------------------------------------------------------------
1  | 555       | 132333312 | 1             | 1.12.2013 | chit-chat   |
2  | 555       | 172777441 | 2             | 1.12.2013 | serios talk |
3  | 555       | 172777441 | 2             | 2.12.2013 | conversation|
----------------------------------------------------------------------

我正在展示电话列表，不是按字母顺序排列，而是按通话频率排列。因此，无论谁从本地电话555打来，都会在最上方看到他/她经常通话的人。这是我用于此目的的MySQL查询：

SELECT p.fname, p.lname, c.notes, COUNT(c.dst_public_id) AS amount
FROM people p
JOIN calls c ON c.dst_public_id = p.id
WHERE phones != ''
GROUP BY p.id
ORDER BY amount DESC, p.lname ASC

结果是我在电话列表的顶部得到了第二个人，第一个人排在第二位（我计算每个公众人物接到的电话数量，并按此数量排序）。以下是查询结果：

--------------------------------
fname | lname | notes | amount |
--------------------------------
Marry | White |       |  2     |
John  | Black |       |  1     |
--------------------------------

这很好。但是我希望始终显示与该人最后一次对话中所做的最后一条注释。我可能可以为每个人制作完全分离的MySQL查询，请求每个人的最后记录并从其中获取数据，类似于：

SELECT notes FROM calls WHERE dst_public_id = 2 ORDER BY date DESC LIMIT 1

我可以在我的PHP脚本中添加第一个查询结果，但是否有一种方法可以使用单个查询完成?

- user164863

---

这个答案可能是你在寻找的：https://dev59.com/aXM_5IYBdhLWcg3wmkQK - Aiias

Answer 1

您可以使用以下MySQL技巧，使用group_concat()和substring_index()获取最后一个注释：

SELECT p.fname, p.lname, c.notes, COUNT(c.dst_public_id) AS amount,
       substring_index(group_concat(notes order by id desc separator '^'), '^', 1) as last_notes
FROM people p JOIN
     calls c
     ON c.dst_public_id = p.id
WHERE phones != ''
GROUP BY p.id
ORDER BY amount DESC, p.lname ASC;

为了使这个方法有效，你需要选择一个在笔记中不会出现的分隔符。我已经选择了'^'作为分隔符。

- Gordon Linoff

---

这个有多高效？它真的是在连接每个电话的所有注释，只为了找到最后一个吗？我仍然想知道我的查询是否也是一种解决方案（我看到许多数据库管理员采取了这种方法），不幸的是现在无法测试它。 - peter.petrov

我认为它能正常工作，因为您没有10000个用户，每个用户大约有1000个调用或者更多。不确定您的数据库中有多少数据。无论如何，我不知道src_phones是什么。在您的问题中，您写了scr_phones。我的方法使用分组和嵌套查询。使用这两个应该可以满足您的需求。我只是好奇，在没有SQL的情况下，我是否正确地理解了那个查询 :) - peter.petrov

好的，但我想展示那个特定用户的电话，然后再展示其余的。 - user164863

这个 scr_phone 看起来像是一个区号。但也许我错了。 - peter.petrov

好的，如果我理解正确的话，您最初的问题是要按“被叫人”聚合数据。现在您需要一些按“主叫人”聚合的数据。如果是这样，您可以使用类似的技术，我猜，要么从已接受的答案中，要么从我的答案中。 - peter.petrov

Answer 2

你可以试试这个吗？我没有在面前测试它，因为我没有SQL，但你应该能够理解。

SELECT tbl.notes, tbl.amount, p.fname, p.lname

FROM people p

join
(
select t.dst_public_id, c1.notes, t.cnt as amount
from calls c1 join 
   (
      SELECT c2.dst_public_id, count(c2.id) as cnt, max(c2.id) as id
      FROM 
      calls c2
      group by c2.dst_public_id
   ) t on c1.id = t.id
) tbl on p.id = tbl.dst_public_id

WHERE p.phones != ''
GROUP BY p.id
ORDER BY tbl.amount DESC, p.lname ASC

- peter.petrov

---

是的，这个也很好用。我喜欢第一个答案更加紧凑，查询时间相同，但这是真的，我只有大约30个用户和每天可能有数十个电话。 - user164863

好的，谢谢。只是想确认这个查询也可以，或者如果不行就从这里删除它。 - peter.petrov

你知道吗，Peter？如果我重新启动MySQL以清除它的缓存，你的查询实际上会更快，我在你那里得到了0.0318秒，而在Gordon的那里需要4.7秒。 - user164863

嗯，我并不感到惊讶，因为他正在连接字符串以获得你想要的结果。此外，他的代码似乎依赖于MySQL，而我的则不是。我不知道这是否对你的情况有任何影响。祝你好运！ - peter.petrov