两个表的SQL查询，根据两个表列排序结果

I have two tables for registering out-going phone calls.

我有两张桌子，用来登记外出的电话。

First table is people. The scheme is:

第一个表是人。该计划是:

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

Second tables called calls. The scheme is:

第二个表称为调用。该计划是:

----------------------------------------------------------------------
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|
----------------------------------------------------------------------

I'm displaying list of phones for users not in an alphabetical order but by frequency of calls. So whoever calls from local phone 555 sees at the top the people who he/she calls more often. Here is the MySQL query I use for it:

我不是按字母顺序，而是按通话频率为用户显示手机列表。因此，不管是谁用本地电话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

As a result I get person number 2 at the top of the phones list and person number 1 is on second place (I count how many calls are for each individual public and order it by that amount). Here is the query result:

结果，我把二号人物排在电话名单的最前面，一号人物排在第二位(我数了一下每个公众的电话数量，然后按这个数字订购)。查询结果如下:

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

This is all good. But I want always to display last note made on last conersation with that person. I can probably make a totally separated MySQL query for each person requesting last record of each and getting the data from that, something like:

这都是好的。但我总是想要展示最后的音符在最后的对话中与那个人。我可以对每个人做一个完全独立的MySQL查询请求每个人的最后记录并从中获取数据，比如:

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

... and then add the result of the first query inside my PHP script, but is there any way to do it with one single query?

…然后在我的PHP脚本中添加第一个查询的结果，但是有什么方法可以只用一个查询来完成吗?

2 个解决方案

#1

You can get the last note with the following MySQL trick, using group_concat() and substring_index():

使用group_concat()和substring_index()，您可以使用以下MySQL技巧获得最后的注意:

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;

For this to work, you need a separator character that will never appear in the notes. I have chosen '^' for this purpose.

要实现这一点，您需要一个永远不会出现在notes中的分隔符。为此我选择了“^”。

#2

Can you try this? I haven't tested it as I don't have SQL in front of me but you should get the idea.

你可以试试这个吗?我还没有对它进行测试，因为我前面没有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

#1